Date & Time difference

#1 (**permalink**) 28-Jul-2006, 08:12 AM

Hi All,

I am trying to find a formula that I can use in Access to give the time
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10362-date-and-time-difference.html
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
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

#2 (**permalink**) 28-Jul-2006, 08:12 AM

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

--
Good Luck
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
BS"D

"Adam@nospam.com" wrote:

> 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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
>
> [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
>
>

#3 (**permalink**) 28-Jul-2006, 08:12 AM

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

All DateDiff does is provide the difference between the dates in
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
various formats.

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

#4 (**permalink**) 28-Jul-2006, 08:12 AM

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

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

--
Good Luck
BS"D

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362

"Adam@nospam.com" wrote:

> That does not consider the working hours part of my question.
>
> All DateDiff does is provide the difference between the dates in
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
> various formats.
>
> How do I get it to only count when between the hours of 09:00am
> to 5:30pm?
>
>

#5 (**permalink**) 28-Jul-2006, 08:13 AM

Thats awesome, thank you!

#6 (**permalink**) 28-Jul-2006, 08:13 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362

#7 (**permalink**) 28-Jul-2006, 08:13 AM

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

--
Good Luck
BS"D

"Adam@nospam.com" wrote:

> 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.
>
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362

#8 (**permalink**) 28-Jul-2006, 08:13 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
end date and time.

Ofer Cohen wrote:

> What do you expect to get from the difference between 09:00 and 5:30?
>
> --
> Good Luck
> BS"D
>
>
> "Adam@nospam.com" wrote:
>
> > 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.
> >
> >

#9 (**permalink**) 28-Jul-2006, 08:13 AM

Adam@nospam.com wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
> end date and time.

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

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10362
RBrandt at Hunter dot com