Solved

Use EXCEL to calculate time differences

Posted on 2004-08-26
6
354 Views
Last Modified: 2012-05-05
Hopefully someone has done this.  I am looking for the formula to calculate the difference between one date time and another. Converting to days, hours and minutes.  I can do it in VB but need to be able to do it in EXCEL.  Any suggestions?
0
Comment
Question by:mbart
6 Comments
 
LVL 1

Accepted Solution

by:
jutaylor earned 300 total points
ID: 11908206
I came across this formula that might work for you:

Assume your date/times are formatted like: 08/25/2004  18:25:00

(Note: it's one long formula, it just doesn't fit on one line here...)

=CONCATENATE(ROUND(ROUND(((B1-A1)*1440),0)/1440,0)," Days ",TRUNC(MOD(ROUND((B1-A1)*1440,0),1440)/60)," Hours
",ROUND(MOD(ROUND((B1-A1)*1440,0),60),0), " Minutes")
0
 
LVL 1

Expert Comment

by:meski
ID: 11908784
Assuming A1 and A2 have dates in them, and A1 > A2

A3 has "=A1-A2

Now apply custom format like this to A3

"days" dd "hours" hh "minutes" mm "seconds" ss
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 11909731
you could do a macro using VBA in excel to do what you want to achieve, I think that would make things easier for you because then you could work it out exactly how you want to.
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 23

Expert Comment

by:gecko_au2003
ID: 11909739
I also found this website, I hope it helps !

http://www.mathtools.net/Excel/Time_and_Date/
0
 
LVL 23

Expert Comment

by:gecko_au2003
ID: 11909755
you have to register with that website I just posted, just so you know. The URL for the function to work out the time , date diff is below. I think that was done in VBA.

http://www.barasch.com/excel/daymoyear.htm
0
 

Author Comment

by:mbart
ID: 11911867
Thanks for the quick response.  Looks like this will work great.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show, step by step, how to integrate R code into a R Sweave document
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now