Problem with calculating time

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is what I have. I have a time sheet that has an area that calculates
vacation, sick, and comp time hours earned and used each month. It transfers
ending balances automatically to the next sheet/month. This month, March, I
had a beginning comp time balance of 45 minutes/:45 which is shown in cell
O22 as 0:45. So far I have earned 2 hours and 45 minutes/2:45 in comp time
shown in cell O25 as 2:45 and have used 3 hours and 30 minutes/3:30 of comp
time shown in cell O23 as 3:30 leaving me with zero time currently. However
the ending balance shown in cell O26 shows #########. The formula I have for
cell O26 is =O22+O25-O23. I think my problem is that the cells are formatted
so that the values shown are calculated as time of day instead of simply
hours and minutes. Any fixes for this?
 
Lee,
If the cells are formatted as (Custom) hh:mm and times entered as
0:45, 2:30 and 3:30 you should get the correct result.
 
Why is say 70:21 in the cell shown as 1/2/1900 10:21:00 PM in the function
bar? How can I format it to refer to 70:21 as 70 hours and 21 minutes? And
0:45 as zero hours and 45 minutes instead of 12:24:00 AM?
 
Lee,
Format all cells as [h]:mm which will prevent Excel form
"rolling" the hours e.g. 70, into days and hence date format..
 
Topper, the cells are already formatted [h]:mm and 70:21 in the cell still
shows as 1/2/1900 10:21:00 PM in the function bar.
--
Lee Davenport


Toppers said:
Lee,
Format all cells as [h]:mm which will prevent Excel form
"rolling" the hours e.g. 70, into days and hence date format..

Lee said:
Why is say 70:21 in the cell shown as 1/2/1900 10:21:00 PM in the function
bar? How can I format it to refer to 70:21 as 70 hours and 21 minutes? And
0:45 as zero hours and 45 minutes instead of 12:24:00 AM?
 
To stop us going round in circles have a look at:

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


I don't know how (if you can) stop the hh:mm appearing as a date in the
formula bar.


Lee said:
Topper, the cells are already formatted [h]:mm and 70:21 in the cell still
shows as 1/2/1900 10:21:00 PM in the function bar.
--
Lee Davenport


Toppers said:
Lee,
Format all cells as [h]:mm which will prevent Excel form
"rolling" the hours e.g. 70, into days and hence date format..

Lee said:
Why is say 70:21 in the cell shown as 1/2/1900 10:21:00 PM in the function
bar? How can I format it to refer to 70:21 as 70 hours and 21 minutes? And
0:45 as zero hours and 45 minutes instead of 12:24:00 AM?
--
Lee Davenport


:

Lee,
If the cells are formatted as (Custom) hh:mm and times entered as
0:45, 2:30 and 3:30 you should get the correct result.

:

This is what I have. I have a time sheet that has an area that calculates
vacation, sick, and comp time hours earned and used each month. It transfers
ending balances automatically to the next sheet/month. This month, March, I
had a beginning comp time balance of 45 minutes/:45 which is shown in cell
O22 as 0:45. So far I have earned 2 hours and 45 minutes/2:45 in comp time
shown in cell O25 as 2:45 and have used 3 hours and 30 minutes/3:30 of comp
time shown in cell O23 as 3:30 leaving me with zero time currently. However
the ending balance shown in cell O26 shows #########. The formula I have for
cell O26 is =O22+O25-O23. I think my problem is that the cells are formatted
so that the values shown are calculated as time of day instead of simply
hours and minutes. Any fixes for this?
 
None of that seems to work, Thanks for your help anyway.
Can you tell me why I get ######## in a cell that has the formula
=O22+O25-O23 when O22 shows 0:45, O25 shows 2:45, and O23 shows 3:30. All
cells are formatted [h]:mm. I want it to show 0:00 but the program thinks it
is a neg number. Why? This is my last question, I promise.
--
Lee Davenport


Toppers said:
To stop us going round in circles have a look at:

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


I don't know how (if you can) stop the hh:mm appearing as a date in the
formula bar.


Lee said:
Topper, the cells are already formatted [h]:mm and 70:21 in the cell still
shows as 1/2/1900 10:21:00 PM in the function bar.
--
Lee Davenport


Toppers said:
Lee,
Format all cells as [h]:mm which will prevent Excel form
"rolling" the hours e.g. 70, into days and hence date format..

:

Why is say 70:21 in the cell shown as 1/2/1900 10:21:00 PM in the function
bar? How can I format it to refer to 70:21 as 70 hours and 21 minutes? And
0:45 as zero hours and 45 minutes instead of 12:24:00 AM?
--
Lee Davenport


:

Lee,
If the cells are formatted as (Custom) hh:mm and times entered as
0:45, 2:30 and 3:30 you should get the correct result.

:

This is what I have. I have a time sheet that has an area that calculates
vacation, sick, and comp time hours earned and used each month. It transfers
ending balances automatically to the next sheet/month. This month, March, I
had a beginning comp time balance of 45 minutes/:45 which is shown in cell
O22 as 0:45. So far I have earned 2 hours and 45 minutes/2:45 in comp time
shown in cell O25 as 2:45 and have used 3 hours and 30 minutes/3:30 of comp
time shown in cell O23 as 3:30 leaving me with zero time currently. However
the ending balance shown in cell O26 shows #########. The formula I have for
cell O26 is =O22+O25-O23. I think my problem is that the cells are formatted
so that the values shown are calculated as time of day instead of simply
hours and minutes. Any fixes for this?
 
It's a rounding error. It's almost 0, but just a bit less.

If you format the cell as General, you'll see what the value is.

One way to fix this is to round the results:
=round(o22+o25-o23,8)

(I chose 8 just because)


None of that seems to work, Thanks for your help anyway.
Can you tell me why I get ######## in a cell that has the formula
=O22+O25-O23 when O22 shows 0:45, O25 shows 2:45, and O23 shows 3:30. All
cells are formatted [h]:mm. I want it to show 0:00 but the program thinks it
is a neg number. Why? This is my last question, I promise.
--
Lee Davenport

Toppers said:
To stop us going round in circles have a look at:

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


I don't know how (if you can) stop the hh:mm appearing as a date in the
formula bar.


Lee said:
Topper, the cells are already formatted [h]:mm and 70:21 in the cell still
shows as 1/2/1900 10:21:00 PM in the function bar.
--
Lee Davenport


:

Lee,
Format all cells as [h]:mm which will prevent Excel form
"rolling" the hours e.g. 70, into days and hence date format..

:

Why is say 70:21 in the cell shown as 1/2/1900 10:21:00 PM in the function
bar? How can I format it to refer to 70:21 as 70 hours and 21 minutes? And
0:45 as zero hours and 45 minutes instead of 12:24:00 AM?
--
Lee Davenport


:

Lee,
If the cells are formatted as (Custom) hh:mm and times entered as
0:45, 2:30 and 3:30 you should get the correct result.

:

This is what I have. I have a time sheet that has an area that calculates
vacation, sick, and comp time hours earned and used each month. It transfers
ending balances automatically to the next sheet/month. This month, March, I
had a beginning comp time balance of 45 minutes/:45 which is shown in cell
O22 as 0:45. So far I have earned 2 hours and 45 minutes/2:45 in comp time
shown in cell O25 as 2:45 and have used 3 hours and 30 minutes/3:30 of comp
time shown in cell O23 as 3:30 leaving me with zero time currently. However
the ending balance shown in cell O26 shows #########. The formula I have for
cell O26 is =O22+O25-O23. I think my problem is that the cells are formatted
so that the values shown are calculated as time of day instead of simply
hours and minutes. Any fixes for this?
 
Thanks Dave. That worked.
--
Lee Davenport


Dave Peterson said:
It's a rounding error. It's almost 0, but just a bit less.

If you format the cell as General, you'll see what the value is.

One way to fix this is to round the results:
=round(o22+o25-o23,8)

(I chose 8 just because)


None of that seems to work, Thanks for your help anyway.
Can you tell me why I get ######## in a cell that has the formula
=O22+O25-O23 when O22 shows 0:45, O25 shows 2:45, and O23 shows 3:30. All
cells are formatted [h]:mm. I want it to show 0:00 but the program thinks it
is a neg number. Why? This is my last question, I promise.
--
Lee Davenport

Toppers said:
To stop us going round in circles have a look at:

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


I don't know how (if you can) stop the hh:mm appearing as a date in the
formula bar.


:

Topper, the cells are already formatted [h]:mm and 70:21 in the cell still
shows as 1/2/1900 10:21:00 PM in the function bar.
--
Lee Davenport


:

Lee,
Format all cells as [h]:mm which will prevent Excel form
"rolling" the hours e.g. 70, into days and hence date format..

:

Why is say 70:21 in the cell shown as 1/2/1900 10:21:00 PM in the function
bar? How can I format it to refer to 70:21 as 70 hours and 21 minutes? And
0:45 as zero hours and 45 minutes instead of 12:24:00 AM?
--
Lee Davenport


:

Lee,
If the cells are formatted as (Custom) hh:mm and times entered as
0:45, 2:30 and 3:30 you should get the correct result.

:

This is what I have. I have a time sheet that has an area that calculates
vacation, sick, and comp time hours earned and used each month. It transfers
ending balances automatically to the next sheet/month. This month, March, I
had a beginning comp time balance of 45 minutes/:45 which is shown in cell
O22 as 0:45. So far I have earned 2 hours and 45 minutes/2:45 in comp time
shown in cell O25 as 2:45 and have used 3 hours and 30 minutes/3:30 of comp
time shown in cell O23 as 3:30 leaving me with zero time currently. However
the ending balance shown in cell O26 shows #########. The formula I have for
cell O26 is =O22+O25-O23. I think my problem is that the cells are formatted
so that the values shown are calculated as time of day instead of simply
hours and minutes. Any fixes for this?
 
Back
Top