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:

 [TABLE="class: MsoNormalTable, width: 306"]
<tbody>[TR]
[TD="width: 409"] Period 1 - Feb. 26 - Mar. 25, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 2 - Mar. 26 - April 22, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 3 - April 23 - May 20, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 4 - May 21 - June 17, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 5 - June 18 - July 15, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 6 - July 16 - Aug. 12, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 7 - Aug. 13 - Sep. 9, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 8 - Sep. 10 - Oct. 7, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 9 - Oct. 8 - Nov. 4, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 10 - Nov. 5 - Dec. 2, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 11 - Dec. 3 - 30, 2017[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 12 - Dec 31 - Jan 27, 2018
[/TD]
[/TR]
[TR]
[TD="width: 409"] Period 13 Jan. 28 - Feb. 24, 2018
[/TD]
[/TR]
</tbody>[/TABLE]

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