Calculating time (Division)

[t0mrogers]

Hi

I am trying to calculate some large amounts of times and keep getting error messages?

I am trying to sum the below times:

27468:24:55
0:16:22
4514:01:25
14626:12:19
198:54:32

And then want to divide them by 1022077.

I am using the [h]:mm:ss formatting.


I thought it would be simple but keep getting VALUE error message.

Any help would be greatly appreciated

[Pepe Le Mokko]

Have a look at https://support.microsoft.com/en-us/...icrosoft-excel

[Pete_UK]

Two of your values (the first and fourth) are too large for Excel to recognise as times in [h]:mm:ss format, so they will be stored as text values, rather than as real numbers. You will need to convert them to real numbers.

Hope this helps.

Pete

[t0mrogers]

Thanks for your help.

Just used Google Docs and worked fine.

[63falcondude]

You can use this formula:

=SUM(IFERROR(INT(A1:A5)*24+HOUR(A1:A5),LEFT(A1:A5,FIND(":",A1:A5)-1)+0))+SUM(IFERROR(MINUTE(A1:A5),MID(A1:A5,FIND(":",A1:A5)+1,2)+0))/60+SUM(IFERROR(SECOND(A1:A5),RIGHT(A1:A5,2)+0))/3600 Ctrl Shift Enter

To produce the total number of hours.