Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting dd:hh:mm to [h]:mm not working.
I am requesting help finding the number of hours and minutes between two
dates. I do not have to take into account work hours or holidays...just raw hours and minutes. Below is how I am calculating but I arrive at an error. Cell A1 = 8/4/09 7:18 PM Cell B1 = 8/5/09 10:02 PM Cell C1 = "{=TEXT(B1-A1,"dd:hh:mm")}" which converts to 01:02:44, formatted as "37:30:55" Cell D1 = "=CONVERT(C1,"day","hr")" which converts to 25:05, formatted as [h]:mm I was expecting 26:44 as it is the raw spand of hours and minutes between these two dates. Can anyone see what I did wrong ? Thank you in advance, Joe |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting dd:hh:mm to [h]:mm not working.
Why not just
=B1-A1 then use custom formatting [h]:mm and it will return 26:44 -- Regards, Peo Sjoblom "Joe H." <Joe wrote in message ... I am requesting help finding the number of hours and minutes between two dates. I do not have to take into account work hours or holidays...just raw hours and minutes. Below is how I am calculating but I arrive at an error. Cell A1 = 8/4/09 7:18 PM Cell B1 = 8/5/09 10:02 PM Cell C1 = "{=TEXT(B1-A1,"dd:hh:mm")}" which converts to 01:02:44, formatted as "37:30:55" Cell D1 = "=CONVERT(C1,"day","hr")" which converts to 25:05, formatted as [h]:mm I was expecting 26:44 as it is the raw spand of hours and minutes between these two dates. Can anyone see what I did wrong ? Thank you in advance, Joe |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting dd:hh:mm to [h]:mm not working.
=B1-A1
and format to [h]:mm:ss OR =TEXT(B1-A1,"[h]:mm:ss") If this post helps click Yes --------------- Jacob Skaria "Joe H." wrote: I am requesting help finding the number of hours and minutes between two dates. I do not have to take into account work hours or holidays...just raw hours and minutes. Below is how I am calculating but I arrive at an error. Cell A1 = 8/4/09 7:18 PM Cell B1 = 8/5/09 10:02 PM Cell C1 = "{=TEXT(B1-A1,"dd:hh:mm")}" which converts to 01:02:44, formatted as "37:30:55" Cell D1 = "=CONVERT(C1,"day","hr")" which converts to 25:05, formatted as [h]:mm I was expecting 26:44 as it is the raw spand of hours and minutes between these two dates. Can anyone see what I did wrong ? Thank you in advance, Joe |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Converting dd:hh:mm to [h]:mm not working.
That works. Thanks. I was working on other calculations and got myself
cornered in complexity. Appreciate your response. -- Joe H. "Peo Sjoblom" wrote: Why not just =B1-A1 then use custom formatting [h]:mm and it will return 26:44 -- Regards, Peo Sjoblom "Joe H." <Joe wrote in message ... I am requesting help finding the number of hours and minutes between two dates. I do not have to take into account work hours or holidays...just raw hours and minutes. Below is how I am calculating but I arrive at an error. Cell A1 = 8/4/09 7:18 PM Cell B1 = 8/5/09 10:02 PM Cell C1 = "{=TEXT(B1-A1,"dd:hh:mm")}" which converts to 01:02:44, formatted as "37:30:55" Cell D1 = "=CONVERT(C1,"day","hr")" which converts to 25:05, formatted as [h]:mm I was expecting 26:44 as it is the raw spand of hours and minutes between these two dates. Can anyone see what I did wrong ? Thank you in advance, Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting working hours to days | Excel Discussion (Misc queries) | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
Making weekend days working days - the system cuts the working tim | Excel Discussion (Misc queries) | |||
Converting a date to a text field w/o converting it to a julian da | Excel Worksheet Functions | |||
hyperlink between sheets not working after converting to pdf | Excel Discussion (Misc queries) |