# Thread: datediff function. From PM to AM

1. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
Try:
Code:
`DateDiff("n", Starttime, IIf(EndTime > Starttime, EndTime, DateAdd("d", 1, EndTime)))`

3. Registered User
Join Date
Aug 2012
Posts
126
Originally Posted by Sinndho
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.

4. Moderator
Join Date
Mar 2009
Posts
5,442