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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!