Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Date & Time difference

Discussion in 'Information Technology' started by Adam@nospam.com, Jul 28, 2006.

  1. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

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

    --
    Good Luck
    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:
    >
    > [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
    >
    >
     
  4. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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?
     
  5. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

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

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

    --
    Good Luck
    BS"D


    "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
    > various formats.
    >
    > How do I get it to only count when between the hours of 09:00am
    > to 5:30pm?
    >
    >
     
  6. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Thats awesome, thank you!
     
  7. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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.
     
  8. Ofer Cohen

    Ofer Cohen
    Expand Collapse
    Guest

    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. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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.


    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.
    > >
    > >
     
  10. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    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
    > 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...
    RBrandt at Hunter dot com
     
  11. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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 wrote:

    > 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
    > > 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...
    > RBrandt at Hunter dot com
     
  12. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    "Adam@nospam.com" <adamevans81@gmail.com> wrote in message
    news:1149063003.642171.73410@u72g2000cwu.googlegroups.com...
    > 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.

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     

Share This Page