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
  • Last Modified:

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
Asked:
henrikatwork
1 Solution
 
looper8Commented:
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
 
henrikatworkAuthor 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
 
ExpertAdminCommented:
I think you need DateAdd() instead of SUM.

M@
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
FlysterCommented:
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
 
harfangCommented:
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
 
henrikatworkAuthor Commented:
Thank you!

That did work :-)

/Henrik
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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