Fiscal Period by Date

[jdavid1006]

Hey everyone,

I'm drawing a blank here. I have a workbook for a company that runs on a 13 period fiscal year rather than monthly periods and I need to look up the period based on the date. What formula can I use to look up a date and return the period?

I have the following:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Period[/TD]
[/TR]
[TR]
[TD]4/1/18[/TD]
[TD]4/28/18[/TD]
[TD]Period 01[/TD]
[/TR]
[TR]
[TD]4/29/18[/TD]
[TD]5/26/18[/TD]
[TD]Period 02[/TD]
[/TR]
[TR]
[TD]5/27/18[/TD]
[TD]6/23/18[/TD]
[TD]Period 03[/TD]
[/TR]
[TR]
[TD]6/24/18[/TD]
[TD]7/21/18[/TD]
[TD]Period 04[/TD]
[/TR]
[TR]
[TD]7/22/18[/TD]
[TD]8/18/18[/TD]
[TD]Period 05[/TD]
[/TR]
[TR]
[TD]8/19/18[/TD]
[TD]9/15/18[/TD]
[TD]Period 06[/TD]
[/TR]
[TR]
[TD]9/16/18[/TD]
[TD]10/13/18[/TD]
[TD]Period 07[/TD]
[/TR]
[TR]
[TD]10/14/18[/TD]
[TD]11/10/18[/TD]
[TD]Period 08[/TD]
[/TR]
[TR]
[TD]11/11/18[/TD]
[TD]12/8/18[/TD]
[TD]Period 09[/TD]
[/TR]
[TR]
[TD]12/9/18[/TD]
[TD]1/5/19[/TD]
[TD]Period 10[/TD]
[/TR]
[TR]
[TD]1/6/19[/TD]
[TD]2/2/19[/TD]
[TD]Period 11[/TD]
[/TR]
[TR]
[TD]2/3/19[/TD]
[TD]3/2/19[/TD]
[TD]Period 12[/TD]
[/TR]
[TR]
[TD]3/3/19[/TD]
[TD]3/30/19[/TD]
[TD]Period 13
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jasonb75]

Assuming that your table is in A1:C14 with headers in row 1, perhaps something like

=LOOKUP(TODAY(),$A$2:$A$14,$C$2:$C$14)

 

[Dave Patton]

Try a Lookup

Excel 2010
	A	B	C	D
1	Start	Period	Date	Period
2	Apr 01, 18	Period 01	Jun 15, 18	Period 03
3	Apr 29, 18	Period 02	Jun 15, 18	Period 03
5a

Cell Formulas
Range	Formula
D2	=LOOKUP(C2,A2:B14)
D3	=LOOKUP(C2,{43191,"Period 01";43219,"Period 02";43247,"Period 03";43275,"Period 04";43303,"Period 05";43331,"Period 06";43359,"Period 07";43387,"Period 08";43415,"Period 09";43443,"Period 10";43471,"Period 11";43499,"Period 12";43527,"Period 13"})

 

[jdavid1006]

That's perfect! Thank you, Dave!