date within a range

[brad_j_crep]

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

[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 · Accepted solution]

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

[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!

[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.