Calculating Difference between two Date AND Time Values

[lifeis]

Hey guys,
Any help with this would be really appreciated, I have looked around on Google for ages, but amnt having any luck. I have found pages about using DateDiff() to calculate the difference between two Date values, however my values are Date and Time Values, for example, 03/14/2011 11:23.000 and 03/17/2011 03:12.000.
Is there any way to calculate the amount of Hours between these two values?
Thanks alot for your help.
-Craig

 

[Galaxiom]

You must have been unlucky with your searches. DateDiff includes arguments to calculate the difference in any unit of time.

http://www.techonthenet.com/access/functions/date/datediff.php

 

[lifeis]

Thanks for your reply.
I have already had a look at the site that you linked to, but I couldnt see anywhere about using a Date and a time, all the examples use only date.
When I try to run two of my dates and times through DateDiff, I get 'Type mismatch' error.
Should DateDiff accept this format- 2011-03-14 02:49:00?

 

[Galaxiom]

Dates must be supplied as references to date datatype variables or directly in the following format.

#MM/DD/YYYY HH:NN:SS#

 

[lifeis]

Thanks, I believe that is my problem.
I now have to figure out how to seperate the Date value from the Time value, then format them and finally concatenate them back together.
Then with that value, I can use DateDiff to figure the amount of hours between.
My first problem though is how to seperate the Date from the Time. I know how to use Format() to correct the formats; but not how to seperate the values.
Could I read through the first 10 characters and then break that into its own variable? Or is there an easier way?
Thanks once again for your help.

 

[Galaxiom]

No need to separate date and time. Just apply DateDiff directly.

What you see in a datetime field is only the local display scheme. Date is actaully stored as a Double real number as the number of days since 30 December 1899. Time is the decimal part of the number as parts of a day.

Consequently the date manipulation functions work with Date and Time all integrated.

 

[lifeis]

Thanks for your reply.
I have managed to pass through the dates and times and receive an output.
I am using DateDiff("h", 04-01-2011 02:49:00, 04-05-2011 07:50:05)
However, it is returning 2885 hours!
I dont have a clue where it is getting that from. Any ideas?
Thanks again, this is much appreciated.

 

[lifeis]

Just thought I would post my solution to this problem incase anyone ever encounters this same problem in the future.
The reason why DateDiff was returning a crazy amount of hours was simply because of the time format. It was formatted to MM/DD/YYYY - as that is what I had read everywhere, when infact this is not the case, it is set to the machines default time format, and that is what should be used, in my case DD/MM/YYYY as I live in the UK.
Thanks alot for your help.
Craig

 

[ChrisO]

Craig, and for anyone else reading this thread in the future.

Please read again GalaxiomAtHome's post #6.
"Date is actually stored as a Double real number as the number of days since 30 December 1899. Time is the decimal part of the number as parts of a day."

While we can use the DateDiff function for this it is hardly necessary; let's have a closer look at it.

Firstly: as to why the 30 December 1899.
http://blogs.msdn.com/b/ericlippert/archive/2003/09/16/eric-s-complete-guide-to-vt-date.aspx

Secondly: an exposed coding example in VBA.

Sub TestIt()
    Dim datDate1    As Date
    Dim datDate2    As Date
    Dim dblNumDays  As Double
    Dim dblNumHours As Double
    
    datDate1 = #3/14/2011 11:23:00 AM#
    datDate2 = #3/17/2011 3:12:00 AM#
    
    MsgBox CDbl(datDate1)               [color=green]' << 40616.4743055556 (number of days)[/color]
    MsgBox CDbl(datDate2)               [color=green]' << 40619.1333333333 (number of days)[/color]
    
    dblNumDays = datDate1 - datDate2    [color=green]' datDate1 and datDate2 are in days[/color]
    MsgBox dblNumDays                   [color=green]' << -2.65902777777228 Days (Date2 is greater than Date1)[/color]
    
    dblNumHours = dblNumDays * 24       [color=green]' 24 hours per day[/color]
    MsgBox dblNumHours                  [color=green]' << -63.8166666665347 Hours[/color]

    ' If we want the absolute value:
    MsgBox Abs(dblNumHours)             [color=green]' <<  63.8166666665347 Hours[/color]


    [color=green]'-------------------------------------------------------------------------

    ' Or we could simply write all of the above as:[/color]
    dblNumHours = Abs(datDate1 - datDate2) * 24
    MsgBox dblNumHours                  [color=green]' <<  63.8166666665347 Hours[/color]

End Sub

It is important to note that we do not want, or need, a date format involved after the dates are stored.
In essence, after they are stored, they already have a format…the IEEE double format.
Adding another format only adds further needles complexity which can lead to failure.
Use formatted dates for display, not for calculations.

Chris.