Avoid Overthinking

Getting same day of last year using Excel formula

In retail, it’s very common to compare sales of same day, not same date, of last year.  If you are not in retail sector, you may wonder what is the difference between same day and same date of last year.

Let’s look at example:

Assume today is 2018/08/11, and same date of last year is 2017/08/11.  Very straight forward.

But retail people will never compare YoY sales performance in this way.  Why? Because it compares apple to orange.  Think about this, sales on Saturday (2018/08/11) should be better than Friday (2017/08/11).  Make sense?  So we want to compare 2018/08/11 to 2017/08/12 instead.  It’s a Saturday to Saturday comparison.

As such, there is constant demand for Excel formula to get the same day of last year.  In many cases, Excel user would use functions related to date. 

From the above example, we observe that there is +1 day adjustment to previous year in order to get the same day.  Therefore it’s logical for us to think about a solution that returns the same date of last year + 1.

E.g.:

=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())+1)

We can interpret the above formula as follow:

Get the date of the year of today minus 1, i.e. last year, with the same month of today, and the same date of today +1).

For user who knows more date-related functions, we can get the same result with a shorter formula by using EDATE:

=EDATE(TODAY(),-12)+1

EDATE returns the date of 12 months ago (denoted by the second argument –12; negative twelve) and add 1 to the result.

Both formula yield the same result.  And up to this point, you may think the EDATE should be a better solution for its simpler syntax.  Nevertheless both formulas are not robust.  They give incorrect answer when leap year is involved.

What?

Try replace TODAY() with 2020/08/11 to the above formulas and see the results…  They are not correct, are they?  The reason is simple, we have one more day in leap year and we should add 2 days (+2) in the formula instead of 1 day (+1).

Then…

you may start thinking to handle leap year by adding IF statement…… Please STOP a while if you are actually thinking this approach.

Think…

again what do you need:

Getting the same day of last year

…

…

…

and also think about this:

How do you get the same day of last week?

=TODAY()-7

This should be the formula on top of mind with no doubt.  Seriously!

Why, then, you used such Excel functions as DATE, YEAR, MONTH, DAY, EDATE, IF initially?

Probably it is because we are trained to solve problem by using Excel functions; and step into the trap of overthinking unintentionally.

If =TODAY()-7 is the top-of-mind solution for getting same day of last week, then =TODAY() – 7*52 should be the logical solution of getting same day last year.  Isn’t it?

In short, the solution can be as simple as this:

=TODAY() - 364

And it works regardless of leap year or not.

Try not to get lost in Excel functions when basic maths could do