Formulas for Calculating Time

Kelly Pratt

Yes! @Paul Newcome that is perfect - thank you soo much!

Paul Newcome

@Kelly Pratt Happy to help. 👍️

Jared

@Paul Newcome Is it possible to hire you? I came here via a boolean expert search via google and stumbled upon you. I need some Boolean formulas properly written for a project I am working on. Has nothing to do with Smartsheet so my apologies for posting here. I look forward to your reply.

Paul Newcome

@Jared Feel free to reach out to me via LinkedIn and we can speak more there.

John B.

Hi. I see there are many posts about representing time in Smartsheet, and although some solutions come close, I still haven't found one to solve what I think is a very straightforward use case:

I have a sheet that is being used for scheduling multiple event producers on live virtual events.

We have numerous events on any given day, distributed among several producers. I would like to be able to sort the sheet by (1) date and start time, and (2) producer name, date, and start time so we can view the scheduled events for any given day, in order of time. My preferred time format would be 12-hour HH:MM AM or PM. Is it possible to do this and make it sortable?

This post with a formula for converting time into a numerical value based on the 24-hour format comes very close: Issue Sorting by Time — Smartsheet Community. However, military time can be confusing to many people.

Andrée Starå

Hi @John B.

I hope you're well and safe!

Have you explored using a report to sort/group by date/time and producer?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@John B. You would want to use the conversion and hide the column but still sort on it. My suggestion would be to label the column such as "Sort By Time". Even hidden it can still be used to sort, but since it is hidden you don't have to worry about the confusion of 24 hour time.

jagullic

Hi @Paul Newcome, thank you for all the helpful formulas/examples here!

Do you know of a way to add time(s)? We are trying to use a sheet to manage an agenda that changes often, and would like the start and finish options to change automatically when one of the previous meetings changes. I can accomplish this easily in Excel by adding times, but couldn't find anything (yet) in the forums here on how to do that. Preference would be to use 12 hour time, and the agenda doesn't span multiple days.

Example:

Row | Agenda Title| Start Time | End Time | Duration

1 |Topic 1 | 9:00 AM | 9:30 AM | 30

2 |Topic 2 |=Row1 start | =Row 2 start + Row 2 Duration | 45

Any ideas?

Thank you!

Charlene Pons

I am trying to just calculate the total time between the start and end time. This was my formula in excel:

=TIMEVALUE((TEXT(Table1[[#This Row],[End Time]]-Table1[[#This Row],[Start Date]],"h:mm")))

What would my formula be for Smartsheet?

Paul Newcome

@Charlene Pons There are a number of solutions specifically for this outline in this thread.

Paul Newcome

@Leibel Shuchat came up with this dandy one calculating the duration between the system generated Created and Modified columns:

Title: Start Date

Formula:

=DATEONLY(Created@row)

Title: Start Time

Formula:

=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

Title: End Date

Formula:

=DATEONLY(Modified@row)

Title: End Time

Formula:

=IF(SUM(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1))) = 12, 0, SUM(VALUE(LEFT(RIGHT(Modified@row, LEN(Modified@row) - 9), FIND(":", RIGHT(Modified@row, LEN(Modified@row) - 9)) - 1)) * 60)) + SUM(VALUE(MID(Modified@row, FIND(":", Modified@row) + 1, 2))) + IF(RIGHT(Modified@row, 2) = "PM", 720, 0) + 1

Title: Duration Minutes

Formula:

=SUM((SUM([End Date]@row - [Start Date]@row) * 24) * 60) + IF([Start Date]@row < [End Date]@row, SUM(1440 - [Start Time]@row) + [End Time]@row, [End Time]@row - [Start Time]@row)

This final column is the one that will give you the formatted days, hours, minutes:

Title: Duration

Formula:

=IF([Duration Minutes]@row > 1439, ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) + " Day" + IF(ROUNDDOWN(SUM([Duration Minutes]@row / 60) / 24, 0) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 1440) > 59, ROUNDDOWN(SUM(MOD([Duration Minutes]@row, 1440) / 60), 0) + " Hour" + IF(SUM(MOD([Duration Minutes]@row, 1440) / 60) > 1, "s", "") + ", ", "") + IF(MOD([Duration Minutes]@row, 60) > 0, MOD([Duration Minutes]@row, 60) + " Minute" + IF(MOD([Duration Minutes]@row, 60) > 1, "s", ""), "") + IF([Duration Minutes]@row > 0, ".", "")

L_123

@Paul Newcome

Seems very similar to one of my solutions, I used math instead of text parsing after remembering the date is represented by a numeric value, and figuring out how they format it behind the scenes:

returns difference between 2 cells in format D: H: M: S:

="D:" + INT(Created2 - Created1) + " H:" + INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36) + " M:" + INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) + " S:" + INT(60 * (((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) - INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)))

Return each individually:

Days:

INT(Created2 - Created1)

Hours

INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)

Minutes

INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)

Seconds

INT(60 * (((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60) - INT((((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36 - INT(((Created2 - Created1 - INT(Created2 - Created1)) * 1000) / 36)) * 60)))

https://community.smartsheet.com/discussion/68494/list-of-date-formula-i-have-used-repeatedly

Text parsing might be more accurate though, I found after some more rigorous testing that my solution isn't accurate down to the minute, much less the second, once some time has passed between submissions. It might be worth it to mix the two solutions to form a single more optimal one.

Paul Newcome

@L_123 I think I remember at some point a while back we had started exploring leveraging the integers but had noticed that it wasn't quite as accurate long term as we had hoped.

I wonder if the inaccuracy would eventually work its way up into the hours and possibly days as time went on, or were you talking smaller increments such as the difference between 7pm and 8pm?

If the hours are reliable with your method, it is definitely much more efficient than mine.

Paul Newcome

@L_123 I haven't tested this yet, but I just saw someone else posted that the ratio is 2.48...

@remkin posted this in another thread...

Hey,

Want to hear something crazy.

I made it work I took the second timestamp and removed the first timestamp. I found the error was a ratio of 2.48 so I applied that to the formula and it worked.

I honestly dont beleive it its the first win Ive had battling smarthseet limitations. And trust me Ive come across them all.

I hope this helps someone.

Charlene Pons

If it is in the thread, I am not seeing it.

I tried to update it and it still is saying unparseable:

=((VALUE(LEFT([End Time]@row, 2) + (VALUE(RIGHT([End Time]@row, 2))/60)) + ([Date]@row - [Start Date]@row) * 24)) - (VALUE(LEFT([Start Time]@row, 2) + (VALUE(RIGHT([Start Time]@row, 2)) / 60)))

Can someone please help and explain why my formula is giving me an unparseable message? I am tryng to calculate total hours worked: