Calculate time difference in report (expression builder)

Posted on 2006-05-30
Last Modified: 2012-05-05
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;


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

Question by:henrikatwork
    LVL 1

    Expert Comment

    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]
    LVL 1

    Author Comment

    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"

    LVL 7

    Expert Comment

    I think you need DateAdd() instead of SUM.

    LVL 22

    Expert Comment

    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:


    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.

    LVL 58

    Accepted Solution

    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)

    LVL 1

    Author Comment

    Thank you!

    That did work :-)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now