Welcome!

Help with SUM function with Duration of Time

Hello all,

I have been reading through the forum this morning on how to work around time duration in SS. I used the trusty Travis/Ajayi "formula from hell" to get an actual calculation! yeah!!!

My problem is.... trying to SUM the duration column. I have attached a screenshot. I usually use excel... and am not find SS very user friendly. any help is greatly appreciated!

Find more posts tagged with

Comments

L_123

unless overwritten, if the items in the cell are left aligned, they are understood as text, if they are right aligned they are understood as numbers, the same as excel. Wrap your entire formula in the duration column in Value() then a regular sum will work.

alisha_grommes

L@123,

Thank you for the reply!!

Here is the formula I tweaked from the Travis/Ajayi formula:

=IFERROR(INT((((IF(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1) = "12", IF(OR(FIND("a", [Finish Time]2) > 0, FIND("p", [Finish Time]2) > 0), 0, 12), VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1))) + IF(FIND("p", [Finish Time]2) > 0, 12)) * 60 + VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - ((IF(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1) = "12", IF(OR(FIND("a", [Start Time]2) > 0, FIND("p", [Start Time]2) > 0), 0, 12), VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1))) + IF(FIND("p", [Start Time]2) > 0, 12)) * 60 + VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1) = "12", IF(OR(FIND("a", [Finish Time]2) > 0, FIND("p", [Finish Time]2) > 0), 0, 12), VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1))) + IF(FIND("p", [Finish Time]2) > 0, 12)) * 60 + VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - ((IF(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1) = "12", IF(OR(FIND("a", [Start Time]2) > 0, FIND("p", [Start Time]2) > 0), 0, 12), VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1))) + IF(FIND("p", [Start Time]2) > 0, 12)) * 60 + VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2)))) - INT((((IF(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1) = "12", IF(OR(FIND("a", [Finish Time]2) > 0, FIND("p", [Finish Time]2) > 0), 0, 12), VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1))) + IF(FIND("p", [Finish Time]2) > 0, 12)) * 60 + VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - ((IF(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1) = "12", IF(OR(FIND("a", [Start Time]2) > 0, FIND("p", [Start Time]2) > 0), 0, 12), VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1))) + IF(FIND("p", [Start Time]2) > 0, 12)) * 60 + VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1) = "12", IF(OR(FIND("a", [Finish Time]2) > 0, FIND("p", [Finish Time]2) > 0), 0, 12), VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1))) + IF(FIND("p", [Finish Time]2) > 0, 12)) * 60 + VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - ((IF(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1) = "12", IF(OR(FIND("a", [Start Time]2) > 0, FIND("p", [Start Time]2) > 0), 0, 12), VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1))) + IF(FIND("p", [Start Time]2) > 0, 12)) * 60 + VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2)))) - INT((((IF(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1) = "12", IF(OR(FIND("a", [Finish Time]2) > 0, FIND("p", [Finish Time]2) > 0), 0, 12), VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1))) + IF(FIND("p", [Finish Time]2) > 0, 12)) * 60 + VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - ((IF(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1) = "12", IF(OR(FIND("a", [Start Time]2) > 0, FIND("p", [Start Time]2) > 0), 0, 12), VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1))) + IF(FIND("p", [Start Time]2) > 0, 12)) * 60 + VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2)))) / 60) * 60), 0)

If I have read it correctly, there is code in there to format as a number. Am I wrong?

alisha_grommes

Could you explain "wrap the entire formula" ? If I read the code correctly, I am changing the data from text to num throughout the formula with the VALUE command. Here is the formula I am using....

Paul Newcome

The problem is that you are trying to SUM "text". You cannot add :. You will need to separate the hours from the minutes, sum them separately, then merge them back together taking into account for a total of minutes over 60 adding in extra hours.

This is one of the reasons that I like to spread things out using helper columns instead of using a monster formula. If you had used helper columns for the calculations you would have already parsed out the hours from the minutes and you could do the calculations from those columns. As it is, you will need to add a helper column or two.

I would suggest two. Hours and Minutes.

In the Minutes column, it would be a simple

=VALUE(RIGHT(DURATION@row, 2))

In the Hours column you would use

=VALUE(LEFT(DURATION@row, FIND(":", DURATION@row) - 1))

You would then sum the two columns separately. To account for the minutes adding on extra hours, you would want to use something along the lines of

=(SUM(Minutes:Minutes) / 60) - INT(SUM(Minutes:Minutes) / 60)

for the Minutes and then for the hours you would use something like...

=SUM(Hours:Hours) + INT(SUM(Minutes:Minutes) / 60)

Paul Newcome

Also... That Monster formula you used can be simplified to save 773 characters.

Your current formula is 3,179 characters long which is awfully close to the limit of 4,000 characters per cell. If your column names were to be changed to something just a little bit longer and then get into the double and triple digit row references, you could exceed that VERY quickly.

The below formula will calculate time differences assuming the times are within the same day and accounting for am and pm in only 2,406 characters. This will give you a little more wiggle room, and having 20 less cell references means that it will expand in size a lot slower as well.

=IF((VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2))) < 0, (VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1)) + IF(AND(FIND("a", [Finish Time]2) > 0, VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1)) = 12), 12, IF(FIND("a", [Finish Time]2) > 0, 0, IF(AND(FIND("p", [Finish Time]2) > 0, VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1)) = 12), 0, 12)))) - (VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1)) + IF(AND(FIND("a", [Start Time]2) > 0, VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1)) = 12), -12, IF(FIND("a", [Start Time]2) > 0, 0, 12))) - 1, (VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1)) + IF(AND(FIND("a", [Finish Time]2) > 0, VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1)) = 12), 12, IF(FIND("a", [Finish Time]2) > 0, 0, IF(AND(FIND("p", [Finish Time]2) > 0, VALUE(LEFT([Finish Time]2, FIND(":", [Finish Time]2) - 1)) = 12), 0, 12)))) - (VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1)) + IF(AND(FIND("a", [Start Time]2) > 0, VALUE(LEFT([Start Time]2, FIND(":", [Start Time]2) - 1)) = 12), -12, IF(FIND("a", [Start Time]2) > 0, 0, 12)))) + ":" + IF(IF((VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2))) < 0, 60 + (VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2))), (VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2)))) < 10, "0" + IF((VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2))) < 0, 60 + (VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2))), (VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2, FIND(":", [Start Time]2) + 1, 2)))), IF((VALUE(MID([Finish Time]2, FIND(":", [Finish Time]2) + 1, 2))) - (VALUE(MID([Start Time]2<span

alisha_grommes

i'm going back to excel. It's insane to parse everything out just to add back together. Maybe in their next update there will be an option for number or text not number/text

Katharine Dalton

I see this for calculating Minutes/Hours (thank you Paul Newcome!). What would this look like for DAYS?

In the Minutes column, it would be a simple

=VALUE(RIGHT(DURATION@row, 2))

In the Hours column you would use

=VALUE(LEFT(DURATION@row, FIND(":", DURATION@row) - 1))

Sign in to join the conversation!

Quick Links

Trending discussions

Is it possible to edit an email list in an automated monthly report?
I have a monthly report I send out that has a long list of recipients. I know I can edit the recurrence of it being sent, but does anyone know of an easy way to just add or take out people that get my email without having to recreate the whole thing?
User Friendly Search
We would like to allow conference attendees to check themselves in using tablets at the conference center. I know there is a "find in sheet" magnifying glass in table view, but it is not super obvious, especially for non-Smartsheet users. Is there a better way for them to easily find their row among all the attendees so…
2 part automation delay question?
Greetings- I have a automation setup when I select the "status" column drop-down to "completed" I want the row info to be emailed to the contact to submitted it with a follow up then I want it to archive the row (aka move to another sheet). When I run the automation only 1 of the 2 requests happen. The archive happens…