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 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.

"Lee" wrote:

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 Davenport

I've tried all that, hh:mm, [h]:mm, [m], nothing seems to work like i want it.
--
Lee Davenport


"Toppers" wrote:

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.

"Lee" wrote:

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 Davenport

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


"Toppers" wrote:

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.

"Lee" wrote:

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 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..

"Lee" wrote:

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


"Toppers" wrote:

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.

"Lee" wrote:

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 Davenport

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" wrote:

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" wrote:

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


"Toppers" wrote:

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.

"Lee" wrote:

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 Davenport

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" wrote:

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" wrote:

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" wrote:

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


"Toppers" wrote:

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.

"Lee" wrote:

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 Davenport