15

I am doing some work in Excel and am running into a bit of a problem. The instruments I am working with save the date and the time of the measurements and I can read this data into Excel with the following format:

   A           B
1 Date:      Time:
2 12/11/12   2:36:25
3 12/12/12   1:46:14

What I am looking to do is find the difference in the two date/time stamps in mins so that I can create a decay curve from the data. So In Excel, I am looking to Make this (if the number of mins in this example is wrong I just calculated it by hand quickly):

   A           B                 C
1 Date:      Time:       Time Elapsed (Minutes)
2 12/11/12   2:36:25               -
3 12/12/12   1:46:14            1436.82

I Have looked around for a bit and found several methods for the difference in time but they always assume that the dates are the same. I exaggerated the time between my measurements some but that roll over of days is what is causing me grief. Any suggestions or hints as to how to go about this would be great. Even If I could find the difference between the date and times in hrs or days in a decimal format, I could just multiple by a constant to get my answer. Please note, I do have experience with programming and Excel but please explain in details. I sometimes get lost in steps.

Kara
  • 6,115
  • 16
  • 50
  • 57
Linux Rules
  • 473
  • 3
  • 9
  • 17
  • Please realise your question isnt really a programming one but a math one... Adding date to time in the calculation isnt a real issue, just take the difference between both pais and add them up, then even when `time2date1` !! – K_B Dec 11 '12 at 07:43

5 Answers5

23

time and date are both stored as numerical, decimal values (floating point actually). Dates are the whole numbers and time is the decimal part (1/24 = 1 hour, 1/24*1/60 is one minute etc...)

Date-time difference is calculated as:

date2-date1

time2-time1

which will give you the answer in days, now multiply by 24 (hours in day) and then by 60 (minutes in hour) and you are there:

time elapsed = ((date2-date1) + (time2-time1)) * 24 * 60

or 

C3 = ((A3-A2)+(B3-B2))*24*60
Liam
  • 27,717
  • 28
  • 128
  • 190
K_B
  • 3,668
  • 1
  • 19
  • 29
  • 2
    if you do "=datetime2-datetime1" you should also note that he wants to change the format to: "[hh]:mm" For his graph also =TEXT(datetime2-datetime1,"[hh]:mm:ss") should be sufficent. – Offler Dec 11 '12 at 07:45
  • @Offler: well, no he does not, look at the example in his question, it shows the total minutes. Please read the question and the answer completely... – K_B Dec 11 '12 at 07:46
  • That works like a champ. I knew there had to be a simple solution to this, I tried something similar to that but it was not quite doing it. Thanks for helping me out and saving me lots of time!!! – Linux Rules Dec 11 '12 at 09:11
  • Please be aware that the floating point storage of date in Excel can cause rounding errors in the long run (errors carried over in iterative processes will get to significant levels in the time component (seconds and sometimes even the minutes) and can thus cause erroneous dates (with times around midnight) and times!! – K_B Dec 11 '12 at 09:26
4

To add a bit more perspective, Excel stores date and times as serial numbers.

Here is a Reference material to read up.

I would suggest you to use the following:

Combine date to it's time and then do the difference. So it will not cause you any issues of next day or anything.

Please refer to the image with calculations. You may leave your total minutes cell as general or number format.

enter image description here

bonCodigo
  • 14,268
  • 1
  • 48
  • 91
  • "Combine date to it's time and then do the difference. So it will not cause you any issues of next day or anything." In Mathematics it doesnt matter in which order you operate + and - (they are actually the same), So first adding date and time OR first taking the difference is all the same... – K_B Dec 11 '12 at 09:22
  • K_B I am not opposed to your answer, but what harm does it do to help someone with other possible solutions? This is about sharing. – bonCodigo Dec 11 '12 at 10:17
  • You mention **ISSUES** with the combining of dates and times in case of next day etc..., I make the remark that it is **NOT** an ISSUE at all. You raise a non issue! Which I shared with you... – K_B Dec 11 '12 at 10:21
  • This answer would be better if the formula in the screen shot was included in text – Liam Mar 02 '17 at 15:56
0

Neat way to do this is:

=MOD(end-start,1)*24

where start and end are formatted as "09:00" and "17:00"

Midnight shift
If start and end time are on the same day the MOD function does not affect anything. If the end time crosses midnight, and the end is earlier then start (say you start 23PM and finish 1AM, so result is 2 hours), the MOD function flips the sign of the difference.

Note that this formula calculates the difference between two times (actually two dates) as decimal value. If you want to see the result as time, display the result as time (ctrl+shift+2).

https://exceljet.net/formula/time-difference-in-hours-as-decimal-value

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
0
  1. get n day between two dates, by using days360 function =days360(dateA,dateB)
  2. find minute with this formula using timeA as reference =(timeB-$timeA+n*"24:00")*1440
  3. voila you get minutes between two time and dates
riadhifrz
  • 1
-2

I think =TEXT(<cellA> - <cellB>; "[h]:mm:ss") is a more concise answer. This way, you can have your column as a datetime.

Igbanam
  • 5,904
  • 5
  • 44
  • 68