Returning Days and hours between Dates with times stamps

[shaylynch]

Hi all,
I just have a novel issue which is driving me batty....

i have 2 columns that have dates with times stamps.. example is 23/03/2011 15:25:01 & 04/05/2011 13:01:52. I am looking to get the amount of days and hours in between these 2 dates... i have been doing a simple subtraction calculation and changing the format of the return column to dd:hh and it has been working fine... however when the dates seem to extend over 30 days (i think)... the calculation goes hay wire.. for the dates above i should be returning a value of about 41:22 as in 41 days 22 hours but i am getting 2:10..

any help would be much appreciated...

Cheers

Shay

 

[Comfy]

This might help: http://www.cpearson.com/excel/datetime.htm
http://www.cpearson.com/excel/DateTimeWS.htm

 

[Andrew Poulsom]

The dd format won't work because it rolls into February 1900 when it reaches 31. Try:

=INT(A2-A1)&TEXT(MOD(MOD(A2,1)-MOD(A1,1),1)," hh:mm")

 

[shaylynch]

Thanks guys....
Andrew.. that worked a treat...

thanks so much..

Shay

 

[barry houdini]

This formula should be sufficient

=INT(A2-A1)&TEXT(A2-A1," hh:mm")

 

[Andrew Poulsom]

This formula should be sufficient

=INT(A2-A1)&TEXT(A2-A1," hh:mm")

Indeed it should!

 

[shaylynch]

Thanks Barry...and it did indeed..

Shay