Date & Time difference

[Adam]

Hi All,

I am trying to find a formula that I can use in Access to give the time
difference in Hours.

I have the following fields:

[StartDate] , [StartTime] , [EndDate] , [EndTime]

I want to find a formula that will provide a difference in time using
the above fields. But I also need to look between the hours of 09:00am
to 5:30pm.

Does anything exist that can cover this?

I can do this using Crystal reports however I am really struggling with
Access.


Many Thanks

Adam

 

[Guest]

Check help on the function DateDiff, it can give the difference between two
date/time.

 

[Adam]

That does not consider the working hours part of my question.

All DateDiff does is provide the difference between the dates in
various formats.

How do I get it to only count when between the hours of 09:00am
to 5:30pm?

 

[Guest]

In houres
?DateDiff("h",#09:00am#,#5:30pm#)
8

In Minutes
?DateDiff("n",#09:00am#,#5:30pm#)
510

 

[Adam]

Thats awesome, thank you!

 

[Adam]

Actually, this doesnt solve the problem.

This merely counts the time difference between 09:00 and 5:30, this
doesn't answer my original question.

 

[Guest]

What do you expect to get from the difference between 09:00 and 5:30?

 

[Adam]

Well I've got a start date and start time, which I need to work out the
hourly time difference from the end date and end time.

By putting DateDiff("h",#09:00#,#5:30#) it is merely counting the
difference between 09.00 and 5.30, not my start date and time vs. my
end date and time.

 

[Rick Brandt]

Well I've got a start date and start time, which I need to work out
the hourly time difference from the end date and end time.

By putting DateDiff("h",#09:00#,#5:30#) it is merely counting the
difference between 09.00 and 5.30, not my start date and time vs. my
end date and time.

So substitute your full start date and end date where the epxression currenlty
has #09:00# and #5:30#.

 

[Adam]

OK, this is what I've come up with:

Hours Diff:
IIf(DateDiff("d",ConvertDate([CALLDAT_HDW]),ConvertDate([CLSRDAT_HDW]))>=1,DateDiff("h",ConvertDate([CALLDAT_HDW])
& " " & TimeStuff([CALLTME_HDW]),ConvertDate([CLSRDAT_HDW]) & " " &
TimeStuff([CLSRTME_HDW]))-(DateDiff("d",ConvertDate([CALLDAT_HDW]),ConvertDate([CLSRDAT_HDW]))*17),DateDiff("h",ConvertDate([CALLDAT_HDW])
& " " & TimeStuff([CALLTME_HDW]),ConvertDate([CLSRDAT_HDW]) & " " &
TimeStuff([CLSRTME_HDW])))

This assumes that an employee will be working 7 hours a day. It seems
to be working well.

But... does DateDiff take into account weekends? As I'd need to remove
them from the equation.

 

[Rick Brandt]

OK, this is what I've come up with:

Hours Diff:
IIf(DateDiff("d",ConvertDate([CALLDAT_HDW]),ConvertDate([CLSRDAT_HDW]))>=1,DateDiff("h",ConvertDate([CALLDAT_HDW])
& " " & TimeStuff([CALLTME_HDW]),ConvertDate([CLSRDAT_HDW]) & " " &
TimeStuff([CLSRTME_HDW]))-(DateDiff("d",ConvertDate([CALLDAT_HDW]),ConvertDate([CLSRDAT_HDW]))*17),DateDiff("h",ConvertDate([CALLDAT_HDW])
& " " & TimeStuff([CALLTME_HDW]),ConvertDate([CLSRDAT_HDW]) & " " &
TimeStuff([CLSRTME_HDW])))

This assumes that an employee will be working 7 hours a day. It seems
to be working well.

But... does DateDiff take into account weekends? As I'd need to remove
them from the equation.

It doesn't automatically exclude them no. You would have to have to do
something about that yourself.