Create a formula based on time
Good day...I am wondering if there is a formula that can bring back results based on time for example today()-created row brings back days...can I bring back hours or minutes?
Best Answer
-
Not exactly sure what you are looking for but something that can get you started is the below formula which when used on the created or modified columns in Smartsheet will return a number starting from 12:00 AM = 1 through till 11:59 PM = 1440. You can then use these numbers for any calculations...
=IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0) + 1
Answers
-
Not exactly sure what you are looking for but something that can get you started is the below formula which when used on the created or modified columns in Smartsheet will return a number starting from 12:00 AM = 1 through till 11:59 PM = 1440. You can then use these numbers for any calculations...
=IF(SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Created@row, LEN(Created@row) - 9), FIND(":", RIGHT(Created@row, LEN(Created@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))) + IF(RIGHT(Created@row, 2) = "PM", 720, 0) + 1
-
Thanks for the response, we will look at this and let you the outcome... for background, we are a manufacturing facility and we're trying to bring back production results from each shift however both afternoons and midnights pass over the 12:00am time and need a way to separate them.
-
@Leibel S so your formula worked as requested thanks so much! Although when you open the door another request always comes up.
Can you calculate how much time has passed between current time and the created time stamp? For example if the time stamp is 01/18/21 9:06 AM and now its 01/18/21 2:25 PM can you calc to show 5hrs-19m has passed??
Thanks again!
-
John Littler,
I am also looking for help on your last comment regarding time passed between a created time stamp and a modified time stamp. Did you ever come up with anything?
-
Hi Justin,
No we never found a solution and the request / need was not a priority in the end so we moved on sorry!
John
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!