How to prevent Smartsheet from Rounding Dates

mcashoili

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!

Darren Mullen

@mcashoili Sounds like you need to create a "Local Date" column to translate the Smartsheet server time into the local date. I just happen to have made a video about this and it should tell you all you need to know!

https://youtu.be/S-GsCr9wA5g

Darren Mullen

@mcashoili Sounds like you need to create a "Local Date" column to translate the Smartsheet server time into the local date. I just happen to have made a video about this and it should tell you all you need to know!

https://youtu.be/S-GsCr9wA5g

mcashoili

Hey @Darren Mullen, thank you so much! This was perfect and very simple. I remember thinking of the VALUE formula but dismissing it thinking it wouldn't work at all. Appreciate the help!

Darren Mullen

@mcashoili You're welcome! Glad you were helped by my video :)