Hello,
I'm having trouble with Smartsheet rounding Sundays to Mondays and need help with my formula below.
I'm using the Created Date column to capture when a submission is filed into our Sheet and another to capture Weeknumber. However, Smartsheet keeps rounding the Sunday entries to Monday if they occur after a certain time (5 or 6 PM I think.)
Is there a formula I can use to ensure submissions submitted after 5/6 PM on Sundays are counted on Sundays and not Monday? Here's the formula I have so far I grabbed from another community post (using 5 PM):
If date has PM and is greater than 5 (but not equal to 12) it will subtract one day from date. The issue with this formula is that it affects each day instead of just Sunday.
=IF(AND(CONTAINS("P", [Evaluation Date (Created)]@row + ""), VALUE(MID([Evaluation Date (Created)]@row, 10, FIND(":", [Evaluation Date (Created)]@row) - 10)) > 5, NOT(VALUE(MID([Evaluation Date (Created)]@row, 10, FIND(":", [Evaluation Date (Created)]@row) - 10)) = 12)), DATEONLY([Evaluation Date (Created)]@row - 1), IF(AND(CONTAINS("P", [Evaluation Date (Created)]@row + ""), VALUE(MID([Evaluation Date (Created)]@row, 10, FIND(":", [Evaluation Date (Created)]@row) - 10)) = 5, NOT(VALUE(MID([Evaluation Date (Created)]@row, FIND(":", [Evaluation Date (Created)]@row) + 1, 2)) = 0)), DATEONLY([Evaluation Date (Created)]@row - 1), [Evaluation Date (Created)]@row))
Example:
Thank you!
