check if a date falls within a specific month/year

[Kmitchell]

I'm looking for a formula to help me define if a date falls within a specific month and year.

In column B sits the current month and year (December 2010 which will vary with each month). And in column G sits the termination date. I want to say if(g1 falls in the month and year as b1, then give me a 1, otherwise give me a 2. I have tried multiple formulas and I'm not getting it to work properly. Is it the dated if function?

 

[Micwhsct]

Thanks Gerald, the formula works exactly as hoped for. I would like to combine into a nested if statement, as my data covers 4 fiscal years - is there a smarter way of doing this other than creating a humongus formula?

 

[Gerald Higgins]

If you are certain that all dates do actually fall within the 4 years, there are various options.

Nested IF is one option, for example . . .
=if(A1< [year 1 end date],"Year 1",if(A1< [year 2 end date],...and so on.

Another option would be a lookup table.
Specify the year start dates in a table, say in Col B, and your year code in Col C, and then
=vlookup(A1,B1:C4,2,true)

 

[Micwhsct]

Thanks again Gerald, will have a go at this tomorrow hopefully. Building my knowledge with the experts on this forum is fantastic!

 

[Micwhsct]

That solution worked perfectly Gerald. Thanks
I now want to use these results (now a named range "Year1"along with the name of the individual (named range "Names") and take a figure from another column and show this figure under the relevant year column (columns for 2013/14 through 2018/19). Note that each name will have a figure relating to most years. Hopefully you can follow and advise an appropriate formula to summarise the data. I'm thinking index and match but getting a bit lost. Thanks

 

[Gerald Higgins]

Glad you got it working, but which one of my two suggestions did you go with ?

I'm sorry but I don't understand what you mean in your follow up question.

 

[Micwhsct]

I went with the Lookup solution - very convenient!
Per below, I now want to look for the name from a defined range, check the year associated with the first entry and then take that figue and put it into the second table. I will then replicate this formula to check for any amounts in the following year. Hope this helps clarify what I am seeking to do.
Data table
Name 1 2015/16 4.1
Name 2 2014/15 1.0
Name 3 2015/16 2.5
Name 1 2016/17 3.1
Name 3 2017/18 1.5
Result table
Each row will be pre-fixed with the individuals name and then the years should populate as shown.
2014/15 2015/16 2016/17 2017/18
N1 0.0 4.1 3.1 0.0
N2 1.0 0.0 0.0 0.0
N3 0.0 2.5 0.0 1.5



Thanks in advance