Difference between dates, but a bit more complicated
 

Hi I have cells with dates and times together and I am trying to take the
difference of two dates. I have found out how to get the result in
days:hours:minutes. However, I would like to get the results with the month
as weel (months:days:hours:minutes). However, my main problem seems to be
that if there is less than a month, the default is zero. Adding days and
months was a solution to my orginal problem of not being able to change hours
to go past 24. Help with either issue would be appreciated. I have Excel
2002.

Thank you.

Difference between dates, but a bit more complicated
 

Your last point is the easiest to fix. To display more than 24 hours, use a
format like:
[hh]:mm

To display days:hours:minutes, use the format:
dd:hh:mm

Unfortunately, Excel will not display more than 31 days under this format.
Even more unfortunately, there's no comparable [dd]:hh:mm option.

To display more than 31 days, people typically use a formula like:
=int(a1)&":"&text(mod(a1,1),"hh:mm")

Trying to display months adds more complexity. You can't use a format,
because there's no month zero. Even with a value less than 31 days, it would
still display 1 month. The other problem is that months have unequal
lengths. If you *really* need to display the number of months, post back
with examples of what you consider to be the number of months and days
between, say, Jan 29 and Mar 1.

Regards,
Fred.

"Leyla" wrote in message
...
Hi I have cells with dates and times together and I am trying to take the
difference of two dates. I have found out how to get the result in
days:hours:minutes. However, I would like to get the results with the
month
as weel (months:days:hours:minutes). However, my main problem seems to be
that if there is less than a month, the default is zero. Adding days and
months was a solution to my orginal problem of not being able to change
hours
to go past 24. Help with either issue would be appreciated. I have Excel
2002.

Thank you.

Difference between dates, but a bit more complicated
 

Leyla,

A1=Date1
B1=Date2
C1=B1-A1

and set C1 format as Number (so you should get decimal value in days)
once you have that
Seconds =$C$1*24*60*60
Minutes =$C$1*24*60-MOD($C$1*24*60,1)
Hours =$C$1*24-MOD($C$1*24,1)
Days =$C$1-MOD($C$1,1)
Months =$C$1/30-MOD($C$1/30,1)

Months and Days
=CONCATENATE($C$1/30-MOD($C$1/30,1)," Month
",($C$1-MOD($C$1,1)-(MOD($C$1/30,1)*30))-MOD(($C$1-MOD($C$1,1)-(MOD($C$1/30,1)*30)),1)," Days")

I'm sure there should be simpler formula, but it does not strike my mind now.

hope this helps?

regards,
-kc
*Click YES if this helps.
"Leyla" wrote:

Hi I have cells with dates and times together and I am trying to take the
difference of two dates. I have found out how to get the result in
days:hours:minutes. However, I would like to get the results with the month
as weel (months:days:hours:minutes). However, my main problem seems to be
that if there is less than a month, the default is zero. Adding days and
months was a solution to my orginal problem of not being able to change hours
to go past 24. Help with either issue would be appreciated. I have Excel
2002.

Thank you.