DateDiff Returning Negative Numbers

[vmf]

I have searched this site & Google groups for the solution to my problem &
have found info on the subject, but I have not been successful in
implementing in my query. I am sure the solution is simple but I am a
novice.

Here's my problem & thank you in advance for your help.

I have a query with StartTime & EndTime fields. I created a calculated
field using the following expression (from Allan Browne's site) to calculate
the Elapsed Time in minutes: Minutes: DateDiff("n", [StartDateTime],
[EndDateTime]), however, I am getting negative numbers when my EndTime is
after midnight of the next day.

example:

StartTime=11:50:00 PM
EndTime= 12:00:00 AM
ElapsedTime= -1430

All dates within the same 24 hr period return the correct number of minutes.

 

[John Spencer]

The problem stems from the fact that your times are considered to be on the
same date. If you included a date with the time, then the calculations
would be correct. You may be able to fix this by checking the EndDateTime
and adjusting it by one day if the time is before the StartDateTime

Minutes:
DateDiff("n",[StartDateTime],IIF(EndDateTime<StartDateTime,EndDateTime+1,EndDateTime))

 

[vmf]

Mr. Spencer,

I was able to get my query to work with your help. Thank you again for your
response to my post.

Thank you,
vmf

The problem stems from the fact that your times are considered to be on the
same date. If you included a date with the time, then the calculations
would be correct. You may be able to fix this by checking the EndDateTime
and adjusting it by one day if the time is before the StartDateTime

Minutes:
DateDiff("n",[StartDateTime],IIF(EndDateTime<StartDateTime,EndDateTime+1,EndDateTime))

I have searched this site & Google groups for the solution to my problem &
have found info on the subject, but I have not been successful in
implementing in my query. I am sure the solution is simple but I am a
novice.

Here's my problem & thank you in advance for your help.

I have a query with StartTime & EndTime fields. I created a calculated
field using the following expression (from Allan Browne's site) to
calculate
the Elapsed Time in minutes: Minutes: DateDiff("n", [StartDateTime],
[EndDateTime]), however, I am getting negative numbers when my EndTime is
after midnight of the next day.

example:

StartTime=11:50:00 PM
EndTime= 12:00:00 AM
ElapsedTime= -1430

All dates within the same 24 hr period return the correct number of
minutes.