Finding preceding dates

egrigson

I've created a productivity dashboard for myself and need to find a formula that returns the previous months so I can reference them in a COUNTIFS statement. I have a formula which works fine BUT it doesn't cope when the date crosses a year boundary, ie if the month is September (month 8) my formula will get August's stats using MONTH(TODAY())-1, which returns 7 (as I want). In January however the same formula returns 0, which is January (month 1) minus 1. How can I get this to return 12 for December? 

Thinking about it this will also apply to the year part of the date, so I need a way to create a formula which takes the current month (say Jan 2019) and returns the previous month, including the relevant year (so December 2018).

Thanks.

Paul Newcome

Try something along the lines of...

 

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

 

Basically you are telling it to pull the month from the day before the first of the current month.

egrigson

Pretty ingenious, thanks for the quick answer. That works well to get the previous month (and year) but doesn't work if I want to go back two months, three months etc (I report on the previous six months). I'll try subtracting 28 days for each month in the formula - that would break eventually due to different days per month but for six it'll probably work, even if inelegant. Any other suggestions for scaling to multiple months would be great.

Thanks,

Ed

Paul Newcome

Well Ed... You didn't say you wanted to go back SIX months. Geeze... Lol.

 

I'll play around with it and see if I can figure something out. 

Paul Newcome

See Screenshot below for table.

 

Ok. So I figured out how to reference a table to subtract the correct number of days for each month from today's date based on how many months you want to go back. The formula below is assuming the table is on the same sheet. By changing the number in BOLD/UNDERLINE, you will change how many months you are going back.

.

=MONTH(TODAY(-SUM(COLLECT([Month Days]2:[Month Days]25, [Month Number]2:[Month Number]25, AND(@cell <= INDEX([Month Number]2:[Month Number]25, MATCH(MONTH(TODAY()), [Month Number]2:[Month Number]25, 0)), @cell >= INDEX([Month Number]2:[Month Number]25, MATCH(MONTH(TODAY()) - 6, [Month Number]2:[Month Number]25, 0)))))))

.

Basically what this does is references your month number for today. It then subtracts however many months you want to go back (this table only goes back one year) and locates that number as well. It will then add up all of those days from the [Month Days] column Subtract it from today's date (which gives you a legitimate date to pull the month from) and then gives you the month number.

.

If you are looking to use an actual date you have a few options. To go back 6 months from today, remove " MONTH( " from the beginning and " ) " from the end.

 

If you wanted to use the date referencing the first of that month, you would use 

=DATE(YEAR(rest of formula NOT including MONTH), entire formula, 1)

.

For the year, replace MONTH with YEAR. For the month, leave it as MONTH. for the first, simply enter 1.

.

And fyi... This is for a NON-Leap Year. I am still working on getting it to differentiate between the 28 and the 29 in February if it is a leap year. I've gotten it in basic formulas, but mixing it in to the above formula has proven to be a challenge.

 

Paul Newcome

Figured out the Leap Year issue. Build it into the table itself so that the table will change based off of today's date instead of trying to compare two columns within the main formula...

.

In the [Month Days] column you would enter the following formulas next to the appropriate [Month Number]:

.

-10: 

=IF((YEAR(TODAY()) - 1) - (INT((YEAR(TODAY()) - 1) / 4) * 4) = 0, 29, 28)

.

2:

=IF(YEAR(TODAY()) - (INT(YEAR(TODAY()) / 4) * 4) = 0, 29, 28)

egrigson

Thanks for that Paul, it turns out this is far more long winded than I expected and I really appreciate how much work you put into those formulas above. I've come up with an alternative to return a date x months before a given date.

To return the month number (replace the number in BOLD in all four places to change the number of months you want to go back, six months in this example);

=IF(MONTH([TODAY()) > 6, MONTH(TODAY()) - 6, MONTH(TODAY()) + (12 * (INT(ABS(MONTH(TODAY()) - 6) / 12) + 1) - 6))

To return the year (likewise replace the number in BOLD in both places);

=IF(MONTH(TODAY()) > 6, YEAR(TODAY()), YEAR(TODAY() - (INT(ABS(MONTH(TODAY()) - 6) / 12) + 1))

This has the advantage of not needing a lookup table and it works for any number of months (ie you could go back 36 months). In pseudo code it works like this;

IF ADJUST_MONTH < CURRENT_MONTH THEN //if month queried is this year

RESULT_MONTH = CURRENT_MONTH - ADJUST_MONTH //simply subtract month

RESULT_YEAR = CURRENT_YEAR //year is the same

ELSE

YEAR_ADJUST = (INT(ABS(CURRENT_MONTH - ADJUST MONTH) / 12)) + 1

RESULT_MONTH = CURRENT_MONTH + ((12 * YEAR_ADJUST) - ADJUST_MONTH) //calculate month (in previous year)

RESULT_YEAR = CURRENT_YEAR - (YEAR_ADJUST) //calculate previous year

END IF

Done!

Paul Newcome

I like it. Your's does provide better flexibility for sure. The only tweak that I personally would make is to use a cell reference in place of the number. That way I could just type in the number 1 time without actually having to edit the formula. I have fat fingers sometimes and am liable to break something if I touch it too much. Hahaha.

egrigson

That's exactly what I'm doing, but I left a static number in the formulas above to make it easier to read. Thanks again for all your help.

Ed.

Paul Newcome

Awesome! Glad you found yourself a solution.