Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time calculation (in hh.mm) spanning more than one day
I would like to calculate the difference between two dates and times and have
the result be in hh.mm. So if in Cell A1 I have '8/15/2005 9:30 AM' and if in Cell A2 I have '8/16/2005 11:17 AM' then... I would want the result in Cell A3 to be 26:01. Can anyone tell me what formula I would use to do this? My boss asked me just a few minutes ago! |
#2
|
|||
|
|||
One way:
Format/Cells/Number/Custom [h]:mm In article , "dtencza" wrote: I would like to calculate the difference between two dates and times and have the result be in hh.mm. So if in Cell A1 I have '8/15/2005 9:30 AM' and if in Cell A2 I have '8/16/2005 11:17 AM' then... I would want the result in Cell A3 to be 26:01. Can anyone tell me what formula I would use to do this? My boss asked me just a few minutes ago! |
#3
|
|||
|
|||
I realized after I posted this that Cell A2 should say '8/16/2005 11:31 AM'
in my example. I have the cell formatted the way I want, I just can't get the calculation correct if the dates span over a day If anyone has made a formula that does this and can share it, I'd be really grateful. "JE McGimpsey" wrote: One way: Format/Cells/Number/Custom [h]:mm In article , "dtencza" wrote: I would like to calculate the difference between two dates and times and have the result be in hh.mm. So if in Cell A1 I have '8/15/2005 9:30 AM' and if in Cell A2 I have '8/16/2005 11:17 AM' then... I would want the result in Cell A3 to be 26:01. Can anyone tell me what formula I would use to do this? My boss asked me just a few minutes ago! |
#4
|
|||
|
|||
The formula is simply the larger (later) minus the smaller (earlier):
=A2-A1 And John gave you the custom format to use, so that the results in excess of 24 wouldn't roll over into days. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "dtencza" wrote in message ... I realized after I posted this that Cell A2 should say '8/16/2005 11:31 AM' in my example. I have the cell formatted the way I want, I just can't get the calculation correct if the dates span over a day If anyone has made a formula that does this and can share it, I'd be really grateful. "JE McGimpsey" wrote: One way: Format/Cells/Number/Custom [h]:mm In article , "dtencza" wrote: I would like to calculate the difference between two dates and times and have the result be in hh.mm. So if in Cell A1 I have '8/15/2005 9:30 AM' and if in Cell A2 I have '8/16/2005 11:17 AM' then... I would want the result in Cell A3 to be 26:01. Can anyone tell me what formula I would use to do this? My boss asked me just a few minutes ago! |
#5
|
|||
|
|||
If you've got it formatted the way you want, just subtract:
A1: 8/15/2005 9:30 AM A2: 8/16/2005 11:31 AM A3: =A2 - A1 === 26:01 In article , "dtencza" wrote: I realized after I posted this that Cell A2 should say '8/16/2005 11:31 AM' in my example. I have the cell formatted the way I want, I just can't get the calculation correct if the dates span over a day If anyone has made a formula that does this and can share it, I'd be really grateful. |
#6
|
|||
|
|||
Your math is O.K. - you just need a factor of 24:
8/16/2005 11:31 8/15/2005 9:30 is really 38580.47986 38579.39583 the difference is: 1.084027778 days or 26.01666666 hours -- Gary's Student "dtencza" wrote: I realized after I posted this that Cell A2 should say '8/16/2005 11:31 AM' in my example. I have the cell formatted the way I want, I just can't get the calculation correct if the dates span over a day If anyone has made a formula that does this and can share it, I'd be really grateful. "JE McGimpsey" wrote: One way: Format/Cells/Number/Custom [h]:mm In article , "dtencza" wrote: I would like to calculate the difference between two dates and times and have the result be in hh.mm. So if in Cell A1 I have '8/15/2005 9:30 AM' and if in Cell A2 I have '8/16/2005 11:17 AM' then... I would want the result in Cell A3 to be 26:01. Can anyone tell me what formula I would use to do this? My boss asked me just a few minutes ago! |
#7
|
|||
|
|||
Thanks for writing back so quickly!
I tried that and got 2:01 rather than 26:01. That formula doesn't seem to be taking into account that it spans a day plus the 2 hours and one minute. It is missing 24 hours! "JE McGimpsey" wrote: If you've got it formatted the way you want, just subtract: A1: 8/15/2005 9:30 AM A2: 8/16/2005 11:31 AM A3: =A2 - A1 === 26:01 In article , "dtencza" wrote: I realized after I posted this that Cell A2 should say '8/16/2005 11:31 AM' in my example. I have the cell formatted the way I want, I just can't get the calculation correct if the dates span over a day If anyone has made a formula that does this and can share it, I'd be really grateful. |
#8
|
|||
|
|||
That's because you're not using the format I gave you in a previous
post: [h]:mm In article , "dtencza" wrote: I tried that and got 2:01 rather than 26:01. That formula doesn't seem to be taking into account that it spans a day plus the 2 hours and one minute. It is missing 24 hours! |
#9
|
|||
|
|||
Thanks, everyone. I've got it now. I had the formatting of the cell wrong.
My ' h ' didn't have the [ ] brackets around it, so it wasn't getting the 24 hours. Sorry I didn't catch this when John told me about the formatting. Thanks also to Rag and Gary. I got an "Attaboy" from the boss thanks to you guys! :) "Gary's Student" wrote: Your math is O.K. - you just need a factor of 24: 8/16/2005 11:31 8/15/2005 9:30 is really 38580.47986 38579.39583 the difference is: 1.084027778 days or 26.01666666 hours -- Gary's Student "dtencza" wrote: I realized after I posted this that Cell A2 should say '8/16/2005 11:31 AM' in my example. I have the cell formatted the way I want, I just can't get the calculation correct if the dates span over a day If anyone has made a formula that does this and can share it, I'd be really grateful. "JE McGimpsey" wrote: One way: Format/Cells/Number/Custom [h]:mm In article , "dtencza" wrote: I would like to calculate the difference between two dates and times and have the result be in hh.mm. So if in Cell A1 I have '8/15/2005 9:30 AM' and if in Cell A2 I have '8/16/2005 11:17 AM' then... I would want the result in Cell A3 to be 26:01. Can anyone tell me what formula I would use to do this? My boss asked me just a few minutes ago! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time calculation with military time | Excel Worksheet Functions | |||
Time Calculation in Minutes only ? | Excel Worksheet Functions | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions | |||
Time Calculation | Excel Worksheet Functions | |||
elapsed time calculation | Excel Discussion (Misc queries) |