Difference between two date / time fields 1

[addy]

Hi

I have two cells in Excel with a date and time in them.

One is 05/12/2006 09:49 and the second is
15/03/2007 17:00

I need to calculate the difference between the two cells and display te result as the number of Days, hours & Minutes between the two values.

I have tried a couple of examples found through Google but it is giving me the wrong values.

I have tried =TEXT(M13-B13,"dd:hh:mm") but this gives me 9 days, 7 hours, 11 minutes which is clearly wrong.

Can anyone please help - I urgently need to get this sorted.

Many Thanks.

 

[xlhelp]

You subtract the first date from the last date; format the cell to General. It gives you number of days. The decimal part indicates fraction of the day. Multiplying the decimal part by 24 gives you the number of hours. Again the decimal part of hours indicates minutes and multiplying that by 60 gives you number of minutes.

Should give you 100 days, 7 hours, 11 minutes


Member- AAAA Association Against Acronym Abusers

 

[xlhelp]

BTW, you can't really format it using dd hh mm etc

Member- AAAA Association Against Acronym Abusers

 

[xlhelp]

You could also use datedif or days 360 to get the 100 days. it won't give you the hours and minutes.

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Not sure what you want to have returned. If text is ok, then you could use something like:

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

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

Or ...

in line with John's contribution ...

=TEXT(A2-A1,"0")&" days,"&TEXT(A2-A1,"hh:mm:ss")




Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

Hi,

Kind of depends on what you want to do with the difference. If you only want to DISPLAY or PRINT the days/hours/minutes value, then use John's solution.

However, if you need to calculate, using the difference, use one column for the difference (which will be a pure NUMBER) and another column for the display value, {which will be TEXT).

Why do Dates and Times seem to be so much trouble? faq68-5827




Skip,

_[red][/red]