Solved

Use EXCEL to calculate time differences

Posted on 2004-08-26
337 Views
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
Question by:mbart

LVL 1

Accepted Solution

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

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

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

LVL 23

Expert Comment

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

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

LVL 23

Expert Comment

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

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

Featured Post

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
This is about my first experience with programming Arduino.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …