date within a range

I have a list of quarters for fiscal years (Q1F15) with their start and end dates as one input. I have a second input which is only a date. Ineed to find the quarter and fiscal year (like Q3F15) for each date. Does the second input fall within what quarters begin and end dates? Anyone have any ideas?

Thanks!

Brad

Quarter Start End

Q1F15 02/23/2014 06/14/2014

Q2F15 06/15/2014 09/06/2014

Q3F15 09/07/2014 11/29/2014

Q4F15 11/30/2014 02/28/2015

second input 07/12/2014

Date Time

Join

Preparation

Accepted answers

jdunkerley79

Assuming you have the R tools installed, you can use my 'vlookup' macro to do this.

Need a little prep step to create a number to do the join on but think should work.

Both demo and macro attached.

James

Date VLookUp.yxmd

VLookup Join.yxmc

All comments

MarqueeCrew

Using generate rows you could create all possible dates with their respective quarters. A find and replace could be used to return the quarter.

jdunkerley79

Assuming you have the R tools installed, you can use my 'vlookup' macro to do this.

Need a little prep step to create a number to do the join on but think should work.

Both demo and macro attached.

James

Date VLookUp.yxmd

VLookup Join.yxmc

jack_morgan

I know this has been solved but seems to be a bit overkill for the need. This is a 4 tool workflow that does the same. No macro needed or additional tables.

Take a look and let me know what you think!

Date within range.yxmd

jdunkerley79

That works fwell.

Would be interesting to see which method works best on a massive scale data set.

superjesse

Hello James,

I know you posted this 4 years ago, but this may be what I'm looking for. I have two issues with my data, and maybe some tweak can be made to make it work.

This macro is looking for start date. I only have an end date. So basically I need to look at every thing before that end date then lookup.

I need the 'Level' to look at items less than it. Everything less than 43180 should be populated in this example, but instead its looking for greater than. One step further is I need this by a certain group by field. My data is for people and I need this calc done per person.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703