Selection based on multiple criteria

Greetings all,

I have an expense report that is giving me a headache. Depending departure time, city tier code, and arrival (back to origin) time I have 5 levels of per diem meal expenses. The first break is the destination city tier level, 1,2, or 3. If the date is outbound only, or outbound and back the same day, the time of departure would determine which amount the employee would receive for that particular day, if there was a previous departure but no arrival then the employee would receive the full per diem for that tier level for that day, and finally, if there were an arrival time only, the rate would be one of three for that tier city.

I'm not sure if this is making any sense to anyone and I'm not used to dealing with this many variables, so I would appreciate any help in solving this. I would prefer it to be done with a formula, but I'm not allergic to macros either.

Thanks
Specimen Expense Report.xlsx

Hi Jacob

The tortured mind that dreamt up this scheme obviously had no intention of trying to police it. I wonder exactly which illegal substance (s)he was on?

Excel need to be taught the rules. Time is relatively easy - 1:00 pm although there is a discrepancy between the yellow box "Arrival at or before 13:00 Partial (1) per diem" and B55 "Arrival before 1:00 PM/full per diems")

Excel needs to know which city is in which tier.

Finally, Excel need to know what the heck Levels 1 - 3 relate to - not clear from your post nor from the expense claim.

Once the foregoing is answered, the rest of the problem is easy

(I now begin to understand why the UK tax authorities will accept any "reasonable" (undefined!) amount that has been incurred.)

Regards
Alastair