| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with calculating time
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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" wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
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) Lee wrote: 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" wrote: 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with calculating time
Thanks Dave. That worked.
-- Lee Davenport "Dave Peterson" wrote: 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) Lee wrote: 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" wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating time and pay in excel | Excel Discussion (Misc queries) | |||
| Calculating Time | Excel Discussion (Misc queries) | |||
| Time calculation problem (URGENTProject due) | Excel Discussion (Misc queries) | |||
| problem in date time picker size | Excel Discussion (Misc queries) | |||
| 1/0/1900 10:18:48 AM Time problem | Excel Worksheet Functions | |||
Linear Mode
