PDA

View Full Version : [SOLVED] time calculation problem



louww
02-19-2014, 12:30 PM
Please help!

I'm calculating time cells (hh:mm) and the sum is more than 24 hours.

All the result cells are formatted as [hh]:mm and the reference cells are all formatted as hh:mm

The sum formula for all time cells works fine, except for one sum cell, which gives a result of fraction time e.g.56655.4444

No matter what I do this one cell's sum formula is not working, even though the reference cells are formatted correctly and are numbers isnumber().

Any ideas?

mdmackillop
02-19-2014, 02:11 PM
Can you post a workbook?

louww
02-19-2014, 02:46 PM
Its work in progress, but you should get the idea. The times are pulled from many resources.

All the total formulas work except J36, if I divide by 86400 the totaol does not update automatically

mdmackillop
02-19-2014, 04:08 PM
Change columns H & I to General number format and you will see that you are subtracting year 1900 values from year 2013 values.

louww
02-19-2014, 11:06 PM
Ok, thanks see what you mean, how do I fix this?, column H and I pulls time data from column S and T (with formula =T30-INT(T30), which is formatted as yyyy/mm/dd hh:mm

louww
02-19-2014, 11:09 PM
Sorted! just applied the above-mentioned formula right through all cells in H and I columns