Excel Month Calculation (1 Viewer)

[sychoangel]

I am running around in circles and not getting anywhere...

I am trying to put together a formula that looks at a date &
-if the date is in the current month (i.e. Mar 2014) it returns a 0
- if the date is in the last 13 months (i.e. between Feb 2013 & Feb 2014) it returns a 1
- if the date is > 13 months (i.e. Jan 2013 & prior) it returns a 0

I then can use this to build pivot tables for a monthly report that shows a rolling 13 month chart of the data... the report is easy but I am really struggling getting the formula right

E.g. of data & result required

01 Mar 2014 0
12 Feb 2014 1
11 Nov 2013 1
28 Feb 2013 1
01 Jan 2013 0

 

[Anakardian]

You can try this one. It works in excel 2010 but as for the other version I don't know.
You might need to replace the ";" with "," as I know this can cause problems depending on your system setup.

=IF(AND(MONTH(A2)=MONTH(TODAY());YEAR(A2)=YEAR(TODAY()));0;IF(AND(A2<=EOMONTH(TODAY();-1);A2>EOMONTH(TODAY();-14));1;0))

 

[namliam]

A little more concise but wont work in all functions and replace ; by , when needed:

=IF(A5<EOMONTH(TODAY(),-14)+1,0,IF(A5>=EOMONTH(TODAY(),-1)+1,0,1))

 

[sychoangel]

=IF(AND(MONTH(A2)=MONTH(TODAY());YEAR(A2)=YEAR(TODAY()));0;IF(AND(A2<=EOMONTH(TODAY();-1);A2>EOMONTH(TODAY();-14));1;0))

Hi Anakardian - worked perfectly. I had to replace the ; with , due to my excel 2010 config - but it works well - THANK YOU!

 

[sychoangel]

=IF(A5<EOMONTH(TODAY(),-14)+1,0,IF(A5>=EOMONTH(TODAY(),-1)+1,0,1))

Thanks Namliam - this one worked perfectly also. From a learning perspective - my interpritiation of the formula is :

if less than end of this month - 14 months (+1month) then 0
if greater than end of this month - 1 month (+1month) then 0
otherwise 1

& if my interpritation is correct - why do you have to add the +1month back on v's just minusing 13 months. Am just trying to learn from the logic

 

[Brianwarnock]

The +1 adds 1 day not a month, it is to get the date of the first of the month 13 months back.

Brian

 

[namliam]

What brian said, but in a bit more detail, EOMONTH moves the date to the End of the month at 00:00:00, stricktly speaking that is the start of the day. Meaning if you have a date/time field with 31/Jan/2014 8:00:00 AM and you do EOMONTH(Today(),-2) you will get 31/Jan/2014 00:00:00 and you do < or even <= you will never find the 8:00 am time.
Now stricktly speaking the end of the day is 31/Jan/2014 23:59:59, if you use <= (which very strickly leaves 1 second "error" margin.
Or 01/Feb/2014 00:00:00 using < should always work.

Now using EOMONTH( -14) indead moves back 14 months, but this (theoreticaly) would allow for the last day of the month to "go in Error", adding the 1 day (the +1) ensures things work properly.
Same goes for the "first of this month" by doing EOMONTH(-1) +1

 

[Anakardian]

You are right about the potential time issue however Sychoangel only wrote dates so I assumed it would only have to deal with dates and not times.
If it has to handle that, I would use this one:

=IF(AND(MONTH(A2)=MONTH(TODAY());YEAR(A2)=YEAR(TODAY()));0;IF(AND(rounddown(A2;0)<=EOMONTH(TODAY();-1);rounddown(A2;0)>EOMONTH(TODAY();-14));1;0))

Doing this removes the potential time element from the date and thus avoids the problem.

 

[namliam]

My time comment wasnt aimed towards you Anakardian... was just a bit more detailed explenation on top of that of Brian to the OP's question.

While we are comparing notes, why I wouldnt use your function is because you are mixing and matching things... Which not only convolutes things, but also makes things (somewhat) confusing and overly complex. Also your solution it is harder (I think) to read the actual business rule at large due to your mixing of structures.
But that may just be my thing and if it aint broke dont fix it.... As long as you get the (proper) answer you want you should be good

There are a few other ways of doing this, like for example you could use a VLookup or even a If(Match(),1,0) if you have the list of valid months someplace else (like on another sheet) which may actually be easier to maintain for anyone that is a little less savy with (if) formula's in excel
So many roads lead to Rome, as long as you get to the Colosseum you win