Help with SUM function with Duration of Time

alisha_grommes

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!

 

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....

 =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<

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))