[Question] Formula to calculate fiscal PPeriod from date?

[michellepace]

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.


Calculate_PPeriod.xlsx

Thanks in advance,
Michelle

[Neville Bailey]

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.

[Kevin Smith]

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.