# Calculate seconds between two dates

#### DHS100

##### Board Regular
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:

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Andrew Poulsom

##### MrExcel MVP
There are 24*60*60=86400 seconds in a day. So try a formula like:

=(A2-A1)*86400

#### DHS100

##### Board Regular
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
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
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"

#### Andrew Poulsom

##### MrExcel MVP
Why?

Dim z As Date

You want to return a Long integer don't you?

#### EvilSandwich

##### New Member
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.