Time calculation (in hh.mm) spanning more than one day

[Guest]

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!

 

[JE McGimpsey]

One way:

Format/Cells/Number/Custom [h]:mm

 

[Guest]

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.

One way:

Format/Cells/Number/Custom [h]:mm

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!

 

[RagDyer]

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!
==============================================

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.

One way:

Format/Cells/Number/Custom [h]:mm

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!

 

[JE McGimpsey]

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

 

[Guest]

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

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.

One way:

Format/Cells/Number/Custom [h]:mm

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!

 

[Guest]

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]

That's because you're not using the format I gave you in a previous
post: [h]:mm

 

[Guest]

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!

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

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.

One way:

Format/Cells/Number/Custom [h]:mm

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!