The smartsheet template for a yearly calendar mixes up Feb 29 for March 1-how to fix formula

DRMGH

I am using a template for a yearly calendar and it all works great until March 1st. Its a leap year and at that point it goes haywire. Feb 29 is not seen as March 1. Any suggestions on how to fix?

Here is the formula in the row: =IF(MONTH(Date62) = 1, "January", IF(MONTH(Date62) = 2, "February", IF(MONTH(Date62) = 3, "March", IF(MONTH(Date62) = 4, "April", IF(MONTH(Date62) = 5, "May", IF(MONTH(Date62) = 6, "June", IF(MONTH(Date62) = 7, "July", IF(MONTH(Date62) = 8, "August", IF(MONTH(Date62) = 9, "September", IF(MONTH(Date62) = 10, "October", IF(MONTH(Date62) = 11, "November", IF(MONTH(Date62) = 12, "December"))))))))))))

DKazatsky2

Hi @DRMGH ,

Unfortunately, there is no single formula that will fix this issue - only a complete redo of the sheet will do.

It appears that when this was developed, leap years were not considered. This is evident in the way the Months are coded blue directly, not conditionally. You can also see the year ends December 30th in the leap year scenario - this is also due to the fact that only 365 days were originally considered.

Hope this helps at least lead you somewhere else,

Dave

Toufong Vang

@DRMGH For the last day of February 2024, try DATE(2024,3,1) - 1.

DRMGH

Thanks for your suggestion but the formula in the date column for 2/29/24 is:

=$Date$1 - VALUE(YEARDAY($Date$1) - 60)

The formula in the column for Mar 1 is:

=$Date$1 - VALUE(YEARDAY($Date$1) - 61)

I'm not sure where to enter your formula. As you can see in the pix below, It is missing the blue parent cell marked March.

Smartsheet folks should correct the template---I'm sure I'm not the only one that needs to have this addressed. Or no one has tried to map out dates into March....yet.

Toufong Vang

Feb 29 is not seen as March 1.

Hi, @DRMGH , my apologies. I misunderstood your request. "02/29/24" will never be reported as occurring in "March" because the date exists (is valid), and it occurs in the month of "February". If your processes need to override that fact, then you can use IF() to skip 2/29/2024. For example:

IF(Date@row=DATE(2024,2,29), "March", (your month formula goes here) )

DKazatsky2

Hi @DRMGH ,

Unfortunately, there is no single formula that will fix this issue - only a complete redo of the sheet will do.

It appears that when this was developed, leap years were not considered. This is evident in the way the Months are coded blue directly, not conditionally. You can also see the year ends December 30th in the leap year scenario - this is also due to the fact that only 365 days were originally considered.

Hope this helps at least lead you somewhere else,

Dave

DRMGH

Thanks Dave and Toufong! I appreciate you. I think Smartsheet technical teams should remove that template and replace it with one that can handle leap years! Best, Maria