Hello,
I have a worksheet with 2 columns with date and time. I want to calculate
the difference between date 1 and date 2. The way it is now, I have a formula
that says:
=(B1-A1)*24 and this gives me a result in hours and decimal (for example
7.98).
However, I would like the result to show as #of days/#of hours/#minutes.
Most results will be 0 day but for results with more than one day (24 hours)
I would rather not have for example 120.5 hours but instead "5 days, 3 hours
and 4 minutes" (or whatever it is)...
Any way I can do that?
Thank you,
Caroline

Format your cells with...
d:hh:mm:ss

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Caroline" wrote:

Hello,
I have a worksheet with 2 columns with date and time. I want to calculate
the difference between date 1 and date 2. The way it is now, I have a formula
that says:
=(B1-A1)*24 and this gives me a result in hours and decimal (for example
7.98).
However, I would like the result to show as #of days/#of hours/#minutes.
Most results will be 0 day but for results with more than one day (24 hours)
I would rather not have for example 120.5 hours but instead "5 days, 3 hours
and 4 minutes" (or whatever it is)...
Any way I can do that?
Thank you,
Caroline

This seems a bit clumsy, so maybe someone else can improve on it. With
the earlier date/time in A1 and the later date/time in B1, try this
formula in C1:

=INT(B1-A1)&" day"&IF(INT(B1-A1)=1,", ","s, ")&INT(MOD(B1-A1,1)*24)&"
hours "&INT(MOD((B1-A1)*24,1)*60)&" minutes"

It gives results like:

09/09/2009 23:18 11/09/2009 20:22 1 day, 21 hours 4 minutes
09/09/2009 20:18 11/09/2009 20:22 2 days, 0 hours 4 minutes
10/09/2009 20:22 11/09/2009 20:22 1 day, 0 hours 0 minutes

Hope ths helps.

Pete


On Sep 11, 7:53*pm, Caroline
wrote:
Hello,
I have a worksheet with 2 columns with date and time. I want to calculate
the difference between date 1 and date 2. The way it is now, I have a formula
that says:
=(B1-A1)*24 and this gives me a result in hours and decimal (for example
7.98).
However, I would like the result to show as #of days/#of hours/#minutes.
Most results will be 0 day but for results with more than one day (24 hours)
I would rather not have for example 120.5 hours but instead "5 days, 3 hours
and 4 minutes" (or whatever it is)...
Any way I can do that?
Thank you,
Caroline

=TEXT(B1-A1,"d \da\y\s, h \hr\s m \mi\n\s")



"Caroline" wrote:

Hello,
I have a worksheet with 2 columns with date and time. I want to calculate
the difference between date 1 and date 2. The way it is now, I have a formula
that says:
=(B1-A1)*24 and this gives me a result in hours and decimal (for example
7.98).
However, I would like the result to show as #of days/#of hours/#minutes.
Most results will be 0 day but for results with more than one day (24 hours)
I would rather not have for example 120.5 hours but instead "5 days, 3 hours
and 4 minutes" (or whatever it is)...
Any way I can do that?
Thank you,
Caroline

Falls over when the number of days goes beyond 31.
[And, of course, you don't need the *24.]
--
David Biddulph

"Gary Brown" wrote in message
...
Format your cells with...
d:hh:mm:ss

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Caroline" wrote:

Hello,
I have a worksheet with 2 columns with date and time. I want to calculate
the difference between date 1 and date 2. The way it is now, I have a
formula
that says:
=(B1-A1)*24 and this gives me a result in hours and decimal (for example
7.98).
However, I would like the result to show as #of days/#of hours/#minutes.
Most results will be 0 day but for results with more than one day (24
hours)
I would rather not have for example 120.5 hours but instead "5 days, 3
hours
and 4 minutes" (or whatever it is)...
Any way I can do that?
Thank you,
Caroline