Difference between date and time Excel
I would like to determine the current time from a specified date and time.
If the specified data is: 19/01/2013 16:44:00
and now the time is:19/01/2013 16:45:00
the difference is 0 years 0 months 0 days 0 hours 1 minute 0 seconds
This can be done easily by simply taking the current time and minus the previous time and formatting it, but it is difficult to combine the data, so I want to split each date / time segment into its own cell
Let's say cell A1 has the previous time and cell A2 has the current time
Cells a5 frough f5 will contain 0 years 0 months 0 days 0 hours 1 minute 0 seconds
On a second note on this question, is it possible to force the =NOW()
refresh every x seconds via VBA without interaction?
source to share
You cannot use the function YEAR
to split the year because that will give 1 year between December 31, 2012 and January 1, 2013, when there will be only one day .....
.... Likewise, you might have problems with MONTH and DAY (e.g. MONTH always returns a number between 1 and 12, whereas in this context you would only expect numbers between 0 and 11)
Try DATEDIF
for the first 3, then HOUR
, MINUTE
, SECOND
Justin offers
Assuming start date / time in A2 and end date / time in B2, try these formulas respectively for Years, Months, Days, Hours, Minutes and Seconds
=DATEDIF(A2,B2-(MOD(A2,1)>MOD(B2,1)),"y")
=DATEDIF(A2,B2-(MOD(A2,1)>MOD(B2,1)),"ym")
=DATEDIF(A2,B2-(MOD(A2,1)>MOD(B2,1)),"md")
=HOUR(B2-A2)
=MINUTE(B2-A2)
=SECOND(B2-A2)
format all cells as general
You may get some inconsistencies due to variable month / year length ......
source to share
Private Sub CommandButton1_Click()
DoDateA
End Sub
Sub DoDateA()
Dim D1 As Date, D2 As Date, DC As Date, DS As Date
Dim CA: CA = Array("", "yyyy", "m", "d", "h", "n", "s", "s")
Dim Va%(7), Da(7) As Date, Ci%
D1 = Now + Rnd() * 420 ' vary the * factors for range of dates
D2 = Now + Rnd() * 156
If D1 > D2 Then
[b4] = "Larger"
Else
[b4] = " smaller"
DS = D1
D1 = D2
D2 = DS
End If
[d4] = D1
[e4] = D2
DC = D2
For Ci = 1 To 6
Va(Ci) = DateDiff(CA(Ci), DC, D1)
DC = DateAdd(CA(Ci), Va(Ci), DC)
Va(Ci + 1) = DateDiff(CA(Ci + 1), DC, D1)
If Va(Ci + 1) < 0 Then ' added too much
Va(Ci) = Va(Ci) - 1
DC = DateAdd(CA(Ci), -1, DC)
Cells(9, Ci + 3) = Va(Ci + 1)
Cells(8, Ci + 3) = Format(DC, "yyyy:mm:dd hh:mm:ss")
End If
Da(Ci) = DC
Cells(5, Ci + 3) = CA(Ci)
Cells(6, Ci + 3) = Va(Ci)
Cells(7, Ci + 3) = Format(Da(Ci), "yyyy:mm:dd hh:mm:ss")
Cells(10, Ci + 3) = DateDiff(CA(Ci), D2, D1)
Next Ci
End Sub
source to share