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

 

[Norman Jones]

Hi Gasflower,

Try formatting your total hours as the custom format:

[h]:mm

This will enable you to return worked hours in excess of 24.

 

[Guest]

I triedjust now, but it returns 576, which is the amount of minutes.
Somehow I cannot get a formula to divide this number by 24...

I am thick.....
I think
Regards,
Gasflower

Hi Gasflower,

Try formatting your total hours as the custom format:

[h]:mm

This will enable you to return worked hours in excess of 24.

---
Regards,
Norman

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

 

[Norman Jones]

Hi Gasflower,

I was insufficently explicit.

Enter Start Date and Time in one cell (say A1) and End Date and Time in
another cell (say B1). In C1 enter the formula = B1-A1 and format the cell
C1 using the custom format [h]:mm.


---
Regards,
Norman

I triedjust now, but it returns 576, which is the amount of minutes.
Somehow I cannot get a formula to divide this number by 24...

I am thick.....
I think
Regards,
Gasflower

Hi Gasflower,

Try formatting your total hours as the custom format:

[h]:mm

This will enable you to return worked hours in excess of 24.

---
Regards,
Norman

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

 

[Guest]

Hi Norman,
Thanks for your help.
I found meanwhile via the Worksheet function Forum the solution,brilliant in
its simplicity.
It follows below:

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

Don

Unfortunately, the hyperlinkfunction got lost on pasting it into this
message.

Hi Gasflower,

I was insufficently explicit.

Enter Start Date and Time in one cell (say A1) and End Date and Time in
another cell (say B1). In C1 enter the formula = B1-A1 and format the cell
C1 using the custom format [h]:mm.


---
Regards,
Norman

I triedjust now, but it returns 576, which is the amount of minutes.
Somehow I cannot get a formula to divide this number by 24...

I am thick.....
I think
Regards,
Gasflower

Hi Gasflower,

Try formatting your total hours as the custom format:

[h]:mm

This will enable you to return worked hours in excess of 24.

---
Regards,
Norman



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