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
  • Last Modified:

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?

 2 dates
0
bryanscott53
Asked:
bryanscott53
  • 3
  • 3
  • 2
  • +1
1 Solution
 
slycoderCommented:
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
 
barry houdiniCommented:
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
 
redmondbCommented:
The following formula...
=INT(B1-A1)&" Days "&TEXT(((B1-A1)-INT(B1-A1)),"hh"" hours ""mm""
gives "655 Days 13 hours 52 minutes"


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
redmondbCommented:
Oops...
=INT(B1-A1)&" Days "&TEXT(((B1-A1)-INT(B1-A1)),"hh"" hours ""mm"" minutes""")
0
 
barry houdiniCommented:
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
 
redmondbCommented:
Thanks, Barry. Haste makes waste!
0
 
bryanscott53Author 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
 
barry houdiniCommented:
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
 
bryanscott53Author Commented:
Barry thanks again, so simple when you know how!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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