Time Calculations

[nparsons75]

Hi,

I am trying to calculate times but seem to have an anomaly I am hoping someone can assist with. Please see the table below. From 14:15:00 to 22:30:00 should equate to 8 hours and 15 minutes. However, when I total the hours column I get 7.917 hours?? Why would this be, appreciate any help.

start	end	hours
14:15:00	14:30:00	0.250
14:30:00	18:00:00	3.500
18:20:00	22:30:00	4.167

<tbody>
</tbody>

Thanks

 

[AliGW]

Because your hours column is formatted as a number. Format it as a time the same as the start and end columns.

 

[pjmorris]

Hi, the sums are entirely correct though the representation is slightly odd (in using decimal parts of an hour for the minutes). The reason the sum appears incorrect is that you have 18:20 rather than 18:00 as the start time.

HTH

 

[nparsons75]

Hi, formatting as a time gives these results?

start	end	hours
14:15:00	14:30:00	06:00:00
14:30:00	18:00:00	12:00:00
18:20:00	22:30:00	04:00:00

<tbody>
</tbody>

 

[pjmorris]

Hi, you need to state exactly what formula you have in the hours column and the format code you've used. 22:30 - 18:20 is not 04:00 it is 04:10 so you've possibly not bothered with the minutes element. The correct custom format option would be hh:mm:ss

HTH

 

[nparsons75]

pjmorris, you are correct, we are missing 20 minutes of the data...

This is an error on our part, part of the data is not entered. Thank you for your time and spotting the error.

 

[pjmorris]

my pleasure, thanks for the feedback.