Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: datediff function to include lunch breaks automatically

    I have a database that calculated project costs. In this calculation is labor cost. For total time im using this datediff function to give me total hours worked.

    (DateDiff("n",[StartTime],[EndTime]))/60
    My problem is it does not include there lunch break which is always at 11:00am. I thought I could use an IIF statement that if end time is greater than 1100 subtract an hour but they could start and finish multiple projects after 11:00am so this would cause multiple subtractions. Anyone have any suggestions?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would try:
    Code:
    IIf(hours([StartTime]) <= 11 And hours([EndTime]) >= 12, _
        DateDiff("n", [StartTime], [EndTime]) / 60 - 1, _
        DateDiff("n", [StartTime], [EndTime]) / 60)
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Sinndho View Post
    I would try:
    Code:
    IIf(hours([StartTime]) <= 11 And hours([EndTime]) >= 12, _
        DateDiff("n", [StartTime], [EndTime]) / 60 - 1, _
        DateDiff("n", [StartTime], [EndTime]) / 60)

    It tells me that hours is not defined in the expression. Not sure how i'd define it?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's Hour(), not Hours(), sorry:
    Code:
    IIf(Hour([StartTime]) <= 11 And Hour([EndTime]) >= 12,
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •