Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 237

# calculate time difference

I have two columns of date/times and i need to calculate the difference. some of the dates are different by years, others are just seconds and lots in between.

when i try and subtract one from the other some of the answers do not look right, ie for the one below i get 655.58 which when i divide by 24hrs comes out as 27 days... but the actual amount is much bigger? Can you tell me what i am doing wrong?

0
bryanscott53
• 3
• 3
• 2
• +1
1 Solution

Commented:
Try the undocumented feature:

Where A1 and B1 contain the values in your png:

=DATEDIF(A1,B1,"y")&" years "&DATEDIF(A1,B1,"ym")&" months "&DATEDIF(A1,B1,"md")
&" days"

Source:
http://www.cpearson.com/excel/datedif.aspx
0

Commented:
655.58 looks right - that's the difference in days, you don't need to divide by 24. How do you want to show the difference?

regards, barry
0

Commented:
The following formula...
=INT(B1-A1)&" Days "&TEXT(((B1-A1)-INT(B1-A1)),"hh"" hours ""mm""
gives "655 Days 13 hours 52 minutes"

0

Commented:
Oops...
=INT(B1-A1)&" Days "&TEXT(((B1-A1)-INT(B1-A1)),"hh"" hours ""mm"" minutes""")
0

Commented:
In fact you only need B1-A1 in the text function, Brian, - like this

=INT(B1-A1)&" Days "&TEXT(B1-A1,"h"" hours ""m"" minutes""")

regards, barry
0

Commented:
Thanks, Barry. Haste makes waste!
0

Author Commented:
thanks everyone for your help, i was looking for the info just in hours mintues seconds if possible (HH:MM:SS) as i will be trying to work out an average. both of he options work great but i am not able to average them, sorry i should have said at the start!
0

Commented:
OK Brian,

For that just use a simple subtraction

=B1-A1

and format as [h]:mm:ss

Note square brackets - those are essential

Then you'll see a value like 15733:52:00 or similar for your example

regards, barry
0

Author Commented:
Barry thanks again, so simple when you know how!!
0

## Featured Post

• 3
• 3
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.