Date value is off of Modified Date is off by 1

Something has changed. I have a formula =[Modified Date]@row that captures the last modified date and keeps it static. I do this as I have rows backed up nightly to another sheet if they were modified. Just using Modified Date doesn't work as the date changes once it's on the new sheet. It's now rounding the date up. The backup run nightly at 10 or 11pm CT USA. Unfortunately I don't know when the change happened.

Has anyone else noticed this? I just want the backups to reflect the true last date the row was updated before it was moved. The above ran last night 04/30/2025 so the Last Updated should read 04/30/2025.

=[Modified Date]@row should still work. I shouldn't have to do =LEFT([Modified Date]@row,8) to get the correct value. Right?

Find more posts tagged with

Sheets

Formulas

Accepted answers

David011

Hi Stephanie,

It sounds like you're experiencing an issue with Smartsheet rounding dates, which can be quite frustrating. This problem has been noted by other users as well. Here are a few suggestions to help you resolve this:

Check Time Zone Settings: Ensure that your Smartsheet account and sheets are set to the correct time zone. Sometimes, discrepancies in time zones can cause date rounding issues.
Formula Adjustments: While using =LEFT([Modified Date]@row, 8)can be a workaround, it might not be ideal. You could try using a combination of DATEONLYand VALUEfunctions to ensure the date remains static and accurate
Local Date Column: Create a "Local Date" column to translate the Smartsheet server time into your local date. This can help maintain the correct date without rounding issues
Backup Process: Review your backup process to ensure it captures the correct date. Smartsheet backups include sheet data, comments, and attachments but may not retain original formatting or formulas. You might need to reformat or adjust formulas after importing data from a backup.

https://community.smartsheet.com/discussion/104366/how-to-prevent-smartsheet-from-rounding-dates

https://community.smartsheet.com/discussion/89453/how-do-i-get-round-dates-being-pulled-through-by-formula-issue

If these suggestions don't resolve the issue, you might want to reach out to Smartsheet support for further assistance. They can provide more specific guidance based on your account and setup.

All comments

David011

Hi Stephanie,

Check Time Zone Settings: Ensure that your Smartsheet account and sheets are set to the correct time zone. Sometimes, discrepancies in time zones can cause date rounding issues.
Formula Adjustments: While using =LEFT([Modified Date]@row, 8)can be a workaround, it might not be ideal. You could try using a combination of DATEONLYand VALUEfunctions to ensure the date remains static and accurate
Local Date Column: Create a "Local Date" column to translate the Smartsheet server time into your local date. This can help maintain the correct date without rounding issues
Backup Process: Review your backup process to ensure it captures the correct date. Smartsheet backups include sheet data, comments, and attachments but may not retain original formatting or formulas. You might need to reformat or adjust formulas after importing data from a backup.

https://community.smartsheet.com/discussion/104366/how-to-prevent-smartsheet-from-rounding-dates

https://community.smartsheet.com/discussion/89453/how-do-i-get-round-dates-being-pulled-through-by-formula-issue

If these suggestions don't resolve the issue, you might want to reach out to Smartsheet support for further assistance. They can provide more specific guidance based on your account and setup.

Stephanie Reedy

Thank you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.