Results 1 to 3 of 3

Thread: Formula to calculate fiscal PPeriod from date?

  1. #1
    Bronze Member michellepace's Avatar
    Join Date
    Oct 2018
    Location
    South Africa
    Posts
    131
    Thanks
    15
    Thanked 3 Times in 3 Posts

    Question Formula to calculate fiscal PPeriod from date?

    Good morning,

    I need to calculate PPeriod from a specified date given the FY start. Does anyone know the generic formula to do this in excel? Attached is the sample data. I have not been able to figure this formula out... goodness.

    Click image for larger version. 

Name:	a.jpg 
Views:	19 
Size:	68.0 KB 
ID:	7965
    Calculate_PPeriod.xlsx

    Thanks in advance,
    Michelle

  2. #2
    Diamond Member Neville Bailey's Avatar
    Join Date
    Nov 2010
    Location
    Westville, Durban
    Posts
    2,651
    Thanks
    44
    Thanked 440 Times in 386 Posts
    I would create a vlookup table, with the first column populated with the first date of each period, e.g. 01/01/20, 01/02/20, 01/03/20, etc and the second column with its corresponding period number, e.g. 101, 102, 103, etc.

    Then use the vlookup formula to generate the period number from the date.

    For example, =vlookup(A1,table,2,true), where A1 is the date and "table" is the name of the vlookup table.
    Neville Bailey - Sage Pastel Accounting Consultant
    Phone: 031 521 0080 or 087 095 7895
    neville@accountingsoftwaresupport.co.za
    www.accountingsoftwaresupport.co.za
    IronTree Online Solutions

    "Give every person more in use value than you take from them in cash value."
    WALLACE WATTLES (1860-1911)

  3. #3
    Silver Member
    Join Date
    Aug 2018
    Location
    Johannesburg
    Posts
    221
    Thanks
    0
    Thanked 33 Times in 29 Posts
    Hi Michelle

    The date ranges can be found in the ledgerparameters file - There is a period start and period end date for each period for this year and last year. Extract the dates from there to use in the lookup table Neville is referring to.

Similar Threads

  1. [Question] Formula to calculate leave due in Excel
    By Helizelb in forum Accounting Forum
    Replies: 6
    Last Post: 14-Jan-19, 12:20 PM
  2. Formula to calculate staff costs against profit
    By Jacky in forum Accounting Forum
    Replies: 4
    Last Post: 23-Oct-14, 09:17 AM
  3. Replies: 5
    Last Post: 02-May-12, 09:18 PM

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •