calculating the difference between 2 'Time' values

[ya5irha55an]

Hi, I wonder if someone can help me with the following problem:

I have a table with 2 columns,start time and end time, both containing time values in a four digit format, eg 0930 being 9:30am etc.

I have made a query to convert these into the standard format (eg 0930 becomes 09:30), using left/right and & functions.

Now I use the timevalue function to convert the string , eg 09:30m into a recongnised time value.

The problem is now I want to find the difference between the start and end times- but when i try a simple end_time - start_time formule it returns a long number which i dont understand, rather than just giving me the difference betwen the two times.

Im sure there is a simple way to do this which I dont know, can anyone help me out? Thanks in advance!

 

[YevS]

Try DateDiff("date format", date1, date2)

e.g.

VB code

dim test as string
test = DateDiff("h", #11/12/2007 12:00:00 PM#, Now())

or

Query builder

Test: DateDiff("h", #11/12/2007 12:00:00 PM#, Now())

or

SQL

DateDiff("h", #11/12/2007 12:00:00 PM#, Now()) AS Test


would return the time between today at 12pm and time now in hours and stick it into the string called test.

Not sure how US and EU date format will differ, you'll have to play around.

Here are the settings you could use:
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

Note, you can only use 1 format type per function. So if you need hours and minutes you will need to write 2 functions and put them together.