Flexible date conversion

Hi.

I'm trying to create a Recruitment Dashboard and I would like to compare various attributes year-on-year.

The challenge is that the database gives us a column "Date application submitted" in a weird format and I would like to automatically code it into "MAT TY" (Moving Annual Total This Year) and "MAT YA" (Moving Annual Total Year Ago).

I would like to pull the data out every month and refresh the dashboard to see how it's changing. I am able to pull 24 months of data which will give us everything needed for MAT TY and MAT YA but it won't be ordered by date. The order of applications in the database is random (I think).

Let's assume it's 1st of April 2018 today. I am downloading data from 1st of April 2016 to 31st of March 2018. In this case the MAT TY will be from 1st of April 2017 to 31st of March 2018. MAT YA will include applications from 1st of April 2016 to 31st of March 2017.
Obviously these dates/definitions will move forward when I want to repeat the exercise again on the 1st of May 2018. Then an application from 2nd of April 2017 should be classified at "MAT YA", not "MAT TY" any more.

Data sample is attached. In red you can see an example of a result I would be keen to get (I filled it manually). If it's easier to e.g. put it in 2 columns feel free to change the format.

Example of a task: let's compare what source did the candidates use to apply for a job in the latest 12 months vs previous 12 months. Has there been an increase in "LinkedIn Search"?

Looking forward to hear your solutions!
Cheers,
Anna

You need to record the start and end dates of the periods of interest, so I've assumed that you use F2 to H2 to record 1/04/2016, 1/04/2017 and 1/14/2018 in this example. Also, the "dates" that you show in column C are not proper dates (in the UK) because of the dashes, and so in D2 you can use this formula:

=IF(AND(--SUBSTITUTE(C2,"-","/")>=$F$2,--SUBSTITUTE(C2,"-","/")<$G$2),"YA",IF(AND(--SUBSTITUTE(C2,"-","/")>=$G$2,--SUBSTITUTE(C2,"-","/")<$H$2),"TY",""))

Copy this down as far as you need to.

Hope this helps.

Pete

Thanks for the rep, Anna.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Pete