Forum Discussion

Arul Tresoldi's avatar
Arul Tresoldi
Iron Contributor
Nov 01, 2018

Hours and times values

I really don't know if someone with "the power" will sometime read this: I wonder why Excel has that many types of input availables, but then is so poor when we talk about hours.

 

I had to calculate the hours that a plane stayed parked in the airport; the easiest way is to calculate it doing the sum of the hours from the arrival to the departure. BUT: if the plane stayed for less than 24 hours, it's ok (and you have to arrange the formula in the cases in which the plane arrives at 7PM and leaves at 10AM the morning later, because 7 - 10 will give you #### because is a negative amount and we're talking about timings).

 

So, that's what I mean: excel allows you to work with times, not with hours. If I have to calculate the number of hours I have to work in a week (here is 40), I have to use a special formatting clause to have "40:00"; but then, if I have to calculate 38:51, how can I do it... 

I know that you will almost never calculates more than 60 minutes (is there something that needs that?), but there are lots of things that needs more than 24 hours calcs!

 

EDIT: Yes, sports! A soccer match lasts 90 minutes: how will excel say that someone scored at 78':43"? No one wants to see 01:18:43...

 

So, if anyone working in the programming of Excel will read this, try to figure out how to insert the unit of measure "Hour", not only the time intended as hh:mm:ss

  • Arul, these are different things how to calculate the time and how to represent it. If you would like to sum 22:10 and 07:15 you may show the result as 29:25 if apply [hh]:mm format to it. In background you actually add 0.302 to 0.924 and the result is 1.226.

     

    If you subtract the time you shall say to Excel what your time are in different days if so, but calculations have exactly the same logic, as well as formatting.

  • Arul, these are different things how to calculate the time and how to represent it. If you would like to sum 22:10 and 07:15 you may show the result as 29:25 if apply [hh]:mm format to it. In background you actually add 0.302 to 0.924 and the result is 1.226.

     

    If you subtract the time you shall say to Excel what your time are in different days if so, but calculations have exactly the same logic, as well as formatting.

Resources