Creating new dates automatically - an issue I've run into

[dbl_dbl]

I have a spreadsheet that takes a data dump from another program and automatically creates reports based on today's date. It will look at this month, next month, etc.

All of this relies on a table which takes today's date and breaks it into a weeks and months. However, the formula has no way of knowing when to start a new year.

For example, I am reporting on 10 months running. May is "2019 05", June "2019 06" etc. However, my report is now showing "2019 13".

I would like to make it smart enough to never go over the 12 limit for months, and 53 limit for ISO weeks.

Attached is the sample file.

Thanks!

[Richard Buttrey]

Hi,

Presumably B6 copied across

Please Login or Register  to view this content.

and similarly B5

Please Login or Register  to view this content.

[dbl_dbl]

Hi Richard, is there way to create a list or something similar which will help it figure out that after 2019 12 comes 2019 01?

[Richard Buttrey]

Perhaps B6 copied across

Please Login or Register  to view this content.

similarly for B5 but MOD(26) rather than MOD(12)

[dbl_dbl]

That's pretty neat, haven't seen that before. Only remaining piece would be to find a way for the values above 12 to switch to add +1 to year.

I am going to see if I can make an IF function help there.

[Richard Buttrey]

Maybe B6

Please Login or Register  to view this content.

formatted as "yyyy mm"

and B5

Please Login or Register  to view this content.

[dbl_dbl]

I don't understand how it works, Richard, but it's great. Thanks a lot!