Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate diff. between 2 times
I have followed "Help", but I'm not getting the right results, so something
is wrong. In cell H8 I have the formula =NOW() In cell K8 I typed 3-23 8:30 AM, formatted it as date and time, so it's giving me 23-Mar-09 8:30 AM. In cell K13 I have this formula: =K8-H8, and formatted the cell as "h:mm", which is giving me 21:05 at the moment. Well, between now, which is 11:25 AM 3/20 and 8:30 AM 3/23 is almost 69 hours. Why is it giving me 21 hours?? Thank you Connie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate diff. between 2 times
Format the formula cell as [h]:mm.
The brackets [ ] keep the hours from rolling over at 24 hours. For example: 23:59, when that reaches 24:00 it rolls over to the next day and becomes 0:00. 47:59, when that reaches 48:00 it rolls over and becomes 0:00. The brackets keep that from happening. -- Biff Microsoft Excel MVP "Connie Martin" wrote in message ... I have followed "Help", but I'm not getting the right results, so something is wrong. In cell H8 I have the formula =NOW() In cell K8 I typed 3-23 8:30 AM, formatted it as date and time, so it's giving me 23-Mar-09 8:30 AM. In cell K13 I have this formula: =K8-H8, and formatted the cell as "h:mm", which is giving me 21:05 at the moment. Well, between now, which is 11:25 AM 3/20 and 8:30 AM 3/23 is almost 69 hours. Why is it giving me 21 hours?? Thank you Connie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate diff. between 2 times
Bingo! That's it! Thank you so much! Now I can get on with this
spreadsheet!! Kind regards, Connie "T. Valko" wrote: Format the formula cell as [h]:mm. The brackets [ ] keep the hours from rolling over at 24 hours. For example: 23:59, when that reaches 24:00 it rolls over to the next day and becomes 0:00. 47:59, when that reaches 48:00 it rolls over and becomes 0:00. The brackets keep that from happening. -- Biff Microsoft Excel MVP "Connie Martin" wrote in message ... I have followed "Help", but I'm not getting the right results, so something is wrong. In cell H8 I have the formula =NOW() In cell K8 I typed 3-23 8:30 AM, formatted it as date and time, so it's giving me 23-Mar-09 8:30 AM. In cell K13 I have this formula: =K8-H8, and formatted the cell as "h:mm", which is giving me 21:05 at the moment. Well, between now, which is 11:25 AM 3/20 and 8:30 AM 3/23 is almost 69 hours. Why is it giving me 21 hours?? Thank you Connie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate diff. between 2 times
You're welcome!
Just to clarifiy... At 24 hours the time rolls over and restarts at 0: 36:25 Would display as 12:25 48:15 Would display as 00:15. -- Biff Microsoft Excel MVP "Connie Martin" wrote in message ... Bingo! That's it! Thank you so much! Now I can get on with this spreadsheet!! Kind regards, Connie "T. Valko" wrote: Format the formula cell as [h]:mm. The brackets [ ] keep the hours from rolling over at 24 hours. For example: 23:59, when that reaches 24:00 it rolls over to the next day and becomes 0:00. 47:59, when that reaches 48:00 it rolls over and becomes 0:00. The brackets keep that from happening. -- Biff Microsoft Excel MVP "Connie Martin" wrote in message ... I have followed "Help", but I'm not getting the right results, so something is wrong. In cell H8 I have the formula =NOW() In cell K8 I typed 3-23 8:30 AM, formatted it as date and time, so it's giving me 23-Mar-09 8:30 AM. In cell K13 I have this formula: =K8-H8, and formatted the cell as "h:mm", which is giving me 21:05 at the moment. Well, between now, which is 11:25 AM 3/20 and 8:30 AM 3/23 is almost 69 hours. Why is it giving me 21 hours?? Thank you Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plotting column chart whch colors the bars diff for diff comm | Charts and Charting in Excel | |||
SUMIF formula required to search for 2 diff values in 2 diff colum | Excel Worksheet Functions | |||
Summing up times by key words in diff column | Excel Worksheet Functions | |||
aps3 calculate diff in time and cinvert into another format | Excel Worksheet Functions |