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

    Unanswered: datediff function. From PM to AM

    I have a few fields in my query

    Start time (medium time)
    End Time (medium time)
    First 15 min break (yes/no)
    Lunch (number field)

    I need to calculate the duration of there work day that they were productive.

    Duration: (((DateDiff("n",[StartTime],[EndTme])-IIf([first15minbreak]=True,15,0)-IIf([second15minbreak]=True,15,0))-[LunchBreak]))/60

    Im using the datediff "n" for minutes and then finding the difference between the start and end time. Then i have it subtract 15 minutes if the 15 min breaks are checked yes and last its dividing by 60 to give me total hours. The problem is this is returning a negative number and its incorrect all together.

    6pm to 4pm with two 15min breaks and a 30 min lunch is returning -14+hrs.

    Any thoughts?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    DateDiff("n", Starttime, IIf(EndTime > Starttime, EndTime, DateAdd("d", 1, EndTime)))
    Have a nice day!

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Sinndho View Post
    Try:
    Code:
    DateDiff("n", Starttime, IIf(EndTime > Starttime, EndTime, DateAdd("d", 1, EndTime)))
    AWESOME! Never thought of trying that! Its working and I really appreciate it!

    Heres the final expression for anyone that ever searches for this type of information.

    Duration: ((((DateDiff("n",[StartTime],IIf([EndTme]>[StartTime],[EndTme],DateAdd("d",1,[EndTme])))-IIf([first15minbreak]=True,15,0))-IIf([second15minbreak]=True,15,0))-[LunchBreak])-[Downtime])/60

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    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
  •