Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 860

# Calculate time difference in report (expression builder)

I would like to calculate the time difference between an arrival time and a departure time. When the arrival time is before 00.00 and the departure time is after, the result get wrong for example 23 hours instead of 1 hour difference. (Ex: arrival 23.30, departure 00:30)

I have been trying to write the code like this;

=SUM[departuretime]-[arrivaltime]

this code give the result above.

I have also been trying to write something like;

IIF [arrivaltime] < 00:00 and [departuretime] > 00:00 then =SUM(24:00 - ([departuretime]-[arrivaltime])) else =SUM[departuretime]-[arrivaltime]

This syntax is wrong and I dont know how to write it the right way.

Greatful for help

/Henrik
0
henrikatwork
1 Solution

Commented:
You need to get the right syntax for the IIF function:

IIf(expr, truepart, falsepart)  ...

Replace expr with: [arrivaltime] < 00:00 and [departuretime] > 00:00
Replace truepart with: SUM(24:00 - ([departuretime]-[arrivaltime]))
Replace falsepart with: SUM[departuretime]-[arrivaltime]
0

Author Commented:
I try to write as follows:

= IIf ([arrivalTime] < 00:00 and [departureTime] >= 00:00, SUM(24:00 - ([departureTime] - [arrivalTime])), SUM[departureTime] - [arrivalTime])

but get the message "invalid syntax, you may have entered an operand without an operator"

0

Commented:
I think you need DateAdd() instead of SUM.

M@
0

Commented:
Hi Henrik,

The only way to get accurate numbers is to store both date and time. You can use the following to get what you're looking for:

Minutes:DateDiff("n",[DepartureTime],[ArrivalTime])

This will get you the total of minutes between the 2 times.

TravelTime: [Minutes]\60 & Format([Minutes] Mod 60,"\:00")

This will give you the result in hours & mintes.

Flyster
0

Commented:
Hello henrikatwork

As a matter of  fact, both times will always be >= 0:00. I believe that you want to check whether you get a positive or a negative result:

= ArrivalTime - DepartureTime + IIf(DepartureTime>=ArrivalTime, 1, 0)

This will add one day to travel times that would otherwise show a negative travel time. You can even simplify this a little, by using the fact that True becomes -1 when converted to number:

= ArrivalTime - DepartureTime - (DepartureTime>=ArrivalTime)

Cheers!
(°v°)
0

Author Commented:
Thank you!

That did work :-)

/Henrik
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.