# How to prevent Smartsheet from Rounding Dates

Options
edited 04/24/23

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!