Formulas for Calculating Time

Paul Newcome · 01/15/25

@Andrew Stills I usually use something that will give me "yyyymmddhhmm" as a value (hh is in 24 hour format). Runs into the same limitation when multiple rows are created within the same minute but doesn't require the time zone conversion for the YEARDAY function.

=VALUE(YEAR(Created@row) + "" + RIGHT("0" + MONTH(Created@row), 2) + RIGHT("0" + DAY(Created@row), 2) + LEFT(TIME(RIGHT(Created@row, 8), 1), 2) + RIGHT(TIME(RIGHT(Created@row, 8), 1), 2))

Andrew Stills · 01/16/25

Oh that's great and bit shorter… er, more elegant. Would also allow it to survive past 2099. There goes my plans to start Y2.1K! ;-)

Thanks for passing it along, @Paul Newcome.

Christina09 · 01/16/25

Welcome to 2025!

From 2020 to 2025, this has gotta be the longest thread we have in smartsheet. 🤣

Andrew Stills · 02/27/25

Hey @Paul Newcome quick question. I'm using the previous formula (just a couple posts previous) I made. Even though I'm running the calculation on the (Created) field, I am experiencing a problem where when one person in one time zone edits, the unique number I am attempting to create is changing based on their time zone.

Am I to understand your solution above would mitigate this error?

Thanks!

Formulas for Calculating Time

Comments

Categories