Date arithmetic with specific format.

[djmc]

Hello,

Suppose I have Tue 9:00 PM

I want to do a subtraction and return the number of seconds.

i.e Tue 9:00PM - Tue 6:00 PM returns 180

Also consider rollover

i.e Wed 1:00AM - Tue 6:00 PM returns 25200

Thanks.

 

[SkipVought]

djmc,

Realize that Date/Time Values are NUMBERS. It SEEMS that you have posted CUSTOM FORMATTED Date/Time dosplays. What is the ACTUAL Date/Time VALUES that we are working with?

If you had ACTUAL Date/Time values, the difference in the latter example that you posted, would have to be multiplied by 24*60*60 to return 25200.

How are you getting 25200?

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[djmc]

I am getting data from a field which contains this

Tue Aug 24 21:00:00 2004

I translated it to Tue 9:00 PM in VBA.

i.e Wed 1:00AM - Tue 6:00 PM returns 25200 because theres a 7 hour difference between them, hence 7*60*60 = 25200 secs.


Basically I would use

DateDiff("s","Tue 6:00 PM", "Wed 1:00 AM") or something...
however I get a type mismatch due to the date formatting.

So I need to know what format I could employ, and I'll just use the data field I have with everything and put together the format DateDiff can accept.

 

[SkipVought]

I translated it to Tue 9:00 PM in VBA."

What does that mean? Exactly HOW did you do that? Why did you not just use the date as it was?


Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[djmc]

Okay, I parsed out the date from a file which has NO relation with the date formats in VBA or whatever.

Tue Aug 24 21:00:00 2004 <-- taken from a data file, and inserted into the MS Access db table (that is only one of the files in the record)

All I want to know is if there is a format in VBA where I can employ the DAY(mon,tue,etc) and TIME to perform the DateDiff() and return the number of seconds (just like my previous post as I am getting a type mismatch error due to my formatting)

You asked before why I didnt use the date as it is, well it doesn't work as I get the same error.

Namely,

DateDiff("s", "Tue Aug 24 2004 6:00 PM", "Wed Aug 25 2004 1:00 AM")

 

[SkipVought]

DateDiff("s", "Tue Aug 24 2004 6:00 PM", "Wed Aug 25 2004 1:00 AM")

You are comparing TWO STRINGS! What you posted has NO DATES!

You need REAL DATES

MsgBox DateDiff("s", #8/24/2004 6:00:00 PM#, #8/25/2004 1:00:00 AM#)

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[PHV]

If in VBA access, you may consider something like this:
strBegDate = "Tue Aug 24 2004 6:00 PM"
numBegDate = Eval("#" & Mid(strBegDate, 5) & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244