Calculating dynamic date formula

Vivien Chong

I am trying to work on a formula to show the latest month (ie last month). As we are receiving self-evaluation data at the end of the month, so now in January 2022, we should have received data for December 2021.

What formula should I use to always check the box for last month, especially when we are crossing over to the new year.

In the example above, We are in January 2022 now, it should check the box in row 1 in "Latest Month" column.

Initially I was using =IF(Mth@row = MONTH(TODAY()) - 1, 1)

But when crossing to the new year, this formula is "missing" something.

Can anyone help?

Paul Newcome

Try this...

=IF(AND(Mth@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), Year@row = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), 1)

Bassam Khalil

Hi @Vivien Chong

Hope you are fine, if you have the Submission Date column for your records please try the following formula it will define the last date whatsoever the date of submission or the year of submission:

=IF(MAX([Submission Date]:[Submission Date]) = [Submission Date]@row, 1)

the following screenshot shows the result:

Vivien Chong

Hi @Bassam Khalil thanks for your input. We do not have submission date column as people may be late in submitting data. For Jan 2022, I may be submitting data for both Nov & Dec 2021. So the submitter will indicate the month and year that they are submitting for.

But for our report, we just need to surface all rows for last month. So in the checkbox column, we use =IF(Mth@row = MONTH(TODAY()) - 1, 1), but crossing over to the new year, this formula is not working.

All previous month data will be calculated in metric sheet for average.

Bassam Khalil

Hi @Vivien Chong

you can create a helper column call it [Submission Date] and use the following formula to calculate it's value:

[Submission Date] =IFERROR(DATE(Year@row, MONTH@row, 1), "")

then use the following formula to define the latest input

[Latest Month] =IF(MAX([Submission Date]:[Submission Date]) = [Submission Date]@row, 1)

the following screenshot shows the result:

you can test any date whatsoever the year and month on the following link

Vivien Chong

Hi @Bassam Khalil I think maybe I need to clarify more:

1) When I submit my data today 7 Jan, I could be submitting for December and November 2021.

2) The data month should be captured (not the submission month), ie December or November 2021, instead of January 2022.

3) Therefore, to avoid any error, the submitter will indicate if the data they are submitting is for December or November. (In the Month & Year column - it's manual indication)

4) On our dashboard, we will have 2 sets of data: (a) the latest month data: for the month of January, it will show December 2021 data; (b) 12 months rolling data

5) We would like to have a formula to check the box for last month data: If now is January 2022, it should only display December 2021 data (Month column: 12, Year column: 2021)

6) If someone entered both November and December 2021 data in January 2022, the "latest month" column for November data should not be checked. (we will seldom encounter this situation, such late submission is not encouraged, but it could happened and we do not want to show it)

When we use =IF(Mth@row = MONTH(TODAY()) - 1, 1), it is working fine, till the crossing over from 2021 to 2022. With this formula, it seem that TODAY'S MONTH is 1, and minus 1, it equals 0.

Paul Newcome

Try this...

=IF(AND(Mth@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), Year@row = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), 1)

Vivien Chong

@Paul Newcome the formula works. However I am not quite sure I understand how it works

=IF(AND(Mth@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), Year@row = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), 1)

Am I understanding correctly?

Part 1: (Mth@row = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1), 

==> If today is January, then Mth@row = 12, otherwise Mth@row will be this month - 1

Part 2: Year@row = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0)), 1)

==> I am unable to understand this part.

Paul Newcome

Part 1 you understand correctly. Part 2 is similar logic but for the year.

We take the current year, then if the current month is 1, we subtract 1 from the current year to get previous year (which coincides with the month = 12 bit).

Is that a better explanation? I sometimes struggle to put my brain into words. Haha.

Vivien Chong

@Paul Newcome I can understand now. Thank you so much.

Paul Newcome

Happy to help. 👍️