CODEWITHSUNDEEP
excel
Sonali Kulkarni
Sonali Kulkarni

Reputation: 1

Excel formula to fetch dates as a flag (1,0) for months from now into past 1 yr.As in Past 3month,Past 6 months and Year to date

I have dates column in spreadsheet.I need help with Excel formula to fetch dates as a flag (1,0) for months from now into past 1 yr. As in Past 3 month,Past 6 months and Year to date.

the latest date in the data set is 2/1/2019.This is what I used for fetching for YTD but it fetches only the 12th month from max date in the column as opposed to all the 12 months.

=IF(B3=(DATE(YEAR(MAX($B:$B)),MONTH(MAX($B:$B))-12,1)),1,0)

I used below formula for R3M 3months,but this would make the YTD formula to long and will take long time to run.

=IF(B3=(DATE(YEAR(MAX($B:$B)),MONTH(MAX($B:$B)),1)),1,IF(B3=(DATE(YEAR(MAX($B:$B)),MONTH(MAX($B:$B))-1,1)),1,IF(B3=(DATE(YEAR(MAX($B:$B)),MONTH(MAX($B:$B))-2,1)),1,0)))

Is there any way the formula can be shortened. 

Month,     CMDATE_FLAG,  R3MDATE_FLAG,  YTDMDATE_FLAG,
2/1/2018,   0,    0,             1,
2/1/2018,   0,    0,             1,
3/1/2018,   0,    0,             0,
3/1/2018,   0,    0,             0,

This is what I get for YTD.I expect '1' under YTD flag from Feb 2018- Feb2019(12 months). Thanks for your help.

Upvotes: 0

Views: 696

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60344

Perhaps something like:

=IF(B2>=EDATE(MAX(B:B),-12),1,0)

This formula will include 13 months. You'll have to modify it a bit depending on whether you want to exclude 2018 Feb or 2019 Feb from being marked.

To exclude only 2019 Feb, then try:

=IF(AND($B2<> MAX($B:$B),$B2>=EDATE(MAX($B:$B),-12)),1,0)

To exclude only 2018 Feb, try:

=IF(B3>EDATE(MAX(B:B),-12),1,0)

You can use a similar principal for the other month flags.

The formulas assume the dates in column B are all on the first of the month, as you show in your data example. If that is not the case, the formula may need to be modified.

Upvotes: 0

Related Questions