time calculation problem

[amar502322]

hi everyone,

I come across to receive time table in non standard format sometime. it is very hard to work with them. i ma attaching a file which has few problem which i can't do without asking concerned people. Plz find solution for me
thankx

[joeu2004]

What is wrong?

B3 is 7. C3 is 9:30:00, formatted as h:mm:ss, which is interpreted as 9h 30m 0s. B3*C3 should be 66h 30m 0s. And indeed D3 displays 66:30:00, formatted as [h]:mm:ss.

The only thing wrong that I see is: the formula in D3 should be copied into D4:D7. That should also correct the format, which should be [h]:mm:ss.

[Special-K]

Try

=B2*C2/IF(C2<1,1,24)

and format the result as Custom type [h]:mm:ss

[Special-K]

What is wrong?

B3 is 7. C3 is 9:30:00, formatted as h:mm:ss, which is interpreted as 9h 30m 0s. B3*C3 should be 66h 30m 0s. And indeed D3 displays 66:30:00, formatted as [h]:mm:ss.

The only thing wrong that I see is: the formula in D3 should be copied into D4:D7. That should also correct the format, which should be [h]:mm:ss.

When the hours worked are complete hours they have been entered as an integer not a time (fraction of 1.0) which would normally result in an incorrect calculation much higher than it should be, e.g. 9 is interpreted as 9 days not 9 hours.

Personally I would stick to entering ALL times as hh:mm:ss
It means you can use the same simple formula throughout the spreadsheet, also prevents someone else having to work out whats going on if they look at the spreadsheet and serves as a reminder that you should enter all times as hh:mm:ss.

[amar502322]

special-k you did it. Thanx to everyone giving suggestion.