IIf(expr, truepart, falsepart) ...

Replace expr with: [arrivaltime] < 00:00 and [departuretime] > 00:00

Replace truepart with: SUM(24:00 - ([departuretime]-[arrivalt

Replace falsepart with: SUM[departuretime]-[arriva

Solved

Posted on 2006-05-30

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

I have been trying to write the code like this;

=SUM[departuretime]-[arriv

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]-[arrivalt

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

Greatful for help

/Henrik

6 Comments

IIf(expr, truepart, falsepart) ...

Replace expr with: [arrivaltime] < 00:00 and [departuretime] > 00:00

Replace truepart with: SUM(24:00 - ([departuretime]-[arrivalt

Replace falsepart with: SUM[departuretime]-[arriva

= 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"

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",[Depa

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

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>=Arrival

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>=ArrivalTim

Cheers!

(°v°)

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Dlookup and where condition | 3 | 18 | |

How make page footer shrink vertically after page 1 | 12 | 23 | |

Wrong number of arguments | 2 | 16 | |

MS Access Product Inventory Movement Transfer Form With Separate Records Combined Into one Form | 55 | 42 |

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

Connect with top rated Experts

**15** Experts available now in Live!