utilizing formula to assign a vale based on a date

bh24524 · Oct 31, 2017

My co-worker has a spreadsheet he uses to track employees returning from leaves of absence. We want to try and make it slightly more automated and so are wondering if we can have a Period Number appear in one cell based on the date that is in another cell. He gave me a list of what dates are encompassed in each period. They are as follows:

Period 1 - Feb. 26 - Mar. 25, 2017

Period 2 - Mar. 26 - April 22, 2017

Period 3 - April 23 - May 20, 2017

Period 4 - May 21 - June 17, 2017

Period 5 - June 18 - July 15, 2017

Period 6 - July 16 - Aug. 12, 2017

Period 7 - Aug. 13 - Sep. 9, 2017

Period 8 - Sep. 10 - Oct. 7, 2017

Period 9 - Oct. 8 - Nov. 4, 2017

Period 10 - Nov. 5 - Dec. 2, 2017

Period 11 - Dec. 3 - 30, 2017

Period 12 - Dec 31 - Jan 27, 2018

Period 13 Jan. 28 - Feb. 24, 2018

If it helps any, a period is exactly 4 weeks long. So after February 24th of 2018, it would then reset to period 1 of a new Fiscal year.

Is there a function we can use so that when he enters a date in column J, column B will automatically yield a period number based on what date is in J?

Joe4 · Oct 31, 2017

I would recommend setting up a lookup table structured like this:
Column 1: Start Date of Period (in actual date format)
Column 2: End Date of Period (in actual date format)
Column 3: Period Number

Then you can use VLOOKUP, using the approximate match argument to handle all those inexact matches (where the date you are looking up is not exactly the Start Date).

So, let's say that we use a Named Range on our lookup table, and named it "PERIODS".
Then, when looking up a date in cell A1, the formula would look like:

Code:

=VLOOKUP(A1,PERIODS,3,TRUE)

bh24524 · Oct 31, 2017

Thank you. I wouldn't have thought to use the name manager. I am not very familiar with it to begin with but that gave me a little more practice with it. I am curious, How would this be done using an index match combo?

Joe4 · Oct 31, 2017

I like naming my range when using VLOOKUP. Otherwise, if you are copying the formula down a range of cells, you have to remember to lock the row references in (with absolute range referencing), or they will start to shift, i.e.

Naming ranges is extremely easy. Just select the whole range, go up to the Address bar and enter in any name you want.

Index/Match formulas are not really my forte. I seldom have need to use them, so I am probably not the best person to advise on that.

utilizing formula to assign a vale based on a date

bh24524

Active Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

bh24524

Active Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

utilizing formula to assign a vale based on a date

bh24524

Active Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

bh24524

Active Member

Joe4

MrExcel MVP, Junior Admin

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock