Solved

Use EXCEL to calculate time differences

Posted on 2004-08-26
6
370 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This is about my first experience with programming Arduino.
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Starting up a Project

627 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