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