Calculate seconds between two dates

DHS100

Board Regular
Joined
May 21, 2006
Messages
149
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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?
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top