Return 24:00 when amount of hours worked equals 24:00

[Guest]

I am trying to set up a spread sheet, that keeps track of the hours I work.
The hours vary and quite often span 24 hours.
When I am entering my formaula to calculate the difference between start
time and finish time I get a good result, i.e 16 hours, but when the result
is 24 hours it displays zero.
What I have done:
A1Date started B1Time started C1Date finished D1Time finished
E1Hours work
12/09/04(date) 08:30(time format) 13/09/04(dte) 08:30(tme) ?????
A2Date started B2Time started C2Date finished D2Time finished
E2Hours work
14/09/04 17:00 (time) 15/09/04(date) 09:00(time)
...........

I am using the following formula:
=IF(C1>A1,24+(D1-B1),((24-B1)+D1+(24-D1)+B1))
This results in 16:00 in E2 in time format, in 00:00 in E1 in time format,
but in 24 in number format.
Is there a way to tell the spread sheet to return 24 if the time vallue
returns as 00:00, ie. disply the numberformat when the numerical value of the
sum is 24 and to display timeformat when the numerical value is less then
24?
I did not get any joy with postings or add-inns r from the microsoft
helppages so far...
I would be grateful...
Regards,
Gasflower

 

[Don]

Hi Gasflower,

All you ever wanted to know about dates, times and time
sheets is to be found on these pages....

http://www.cpearson.com/excel/datetime.htm#AddingTimes

HTH,

Don

 

[Guest]

Don,
Thanks for the article, I saved it for difficult times, coz one never knows
when one gets depressed again by the sheer power of Excel.
your solution worked a million.

Love,
Gasflower