Calculate seconds between two dates

DHS100

Board Regular
Joined
May 21, 2006
Messages
145
Hi,

I'm trying to calculate the number of seconds between two variables with dates in them in dd/mm/yy hh:mm:ss format. The variables are dimmed as date. I literally just want a long results giving something like: 338981 seconds.

I'm missing something obvious...thanks for any help!

Also, just out of interest, does anyone know why the following doesn't give a whole number?:

MsgBox Now / 1.15740695036948E-05

The reason I ask is, I thought I could just divide both dates by the number of seconds (1.15740695036948E-05) since date=0 and compare them like that...but I'm getting decimals...?!
 
Last edited:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

DHS100

Board Regular
Joined
May 21, 2006
Messages
145
Hmm...I knew about the seconds in a day, etc., I just never seemed to get the correct answer, but I think this works:


Public Sub asdf()
a = SecondsDiff("09/04/1980 15:33:16", "08/04/1980 15:33:15")
End Sub

Public Function SecondsDiff(BigDate As Date, SmallDate As Date)
Dim MyFullDays As Double
Dim MyRemainingTime As Double

MyFullDays = Int(BigDate - SmallDate)
MyRemainingTime = (BigDate - SmallDate - MyFullDays)

MsgBox MyFullDays * 86400 + ((Hour(MyRemainingTime) * 3600) + (Minute(MyRemainingTime) * 60) + Second(MyRemainingTime))
End Function
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If A1 contains 08/04/1980 15:33:15 and A2 contains 09/04/1980 15:33:16, the formula I posted returns 86401. What's wrong with that?
 

DHS100

Board Regular
Joined
May 21, 2006
Messages
145
I must be missing something here. There are no cells involved, rather a text file is loaded, a timestamp retrieved and saved to a date variable. A code snippit and results I get are:

CODE:
Dim x as date
Dim y As Date
Dim z As Date

x = now
y = DomainVisited_Array(i)
z = (x - y) * 86400 'Gives z = "19/04/1903"
(or)
z = (y - x) * 86400 'Gives z = "13/09/1896"

VALUES:
x = "28/07/2008 09:38:45"
y = "28/07/2008 09:18:40"
 

EvilSandwich

New Member
Joined
Mar 7, 2011
Messages
1
Dim StartTime as Date
Dim EndTime as Date
Dim numSeconds as Long

startDate = Now()
endDate = Now()
numSeconds = DateDiff("s",StartTime, EndTime)

'obviously, you'd want startDate and endDate to be different dates -- either by constructing a a date -- Date("m/d/yyyy h:mm:ss") -- or by passing in two dates.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,782
Messages
5,446,469
Members
405,403
Latest member
horace james

This Week's Hot Topics

Top