Finding the first item in a day.

A_C

Hi,

I hope this finds you well.

I'm putting together a solution that involves a business rule.

If an employee is home based, and drives to to a work site in the morning, then does other trips on the same day, we have a rule that deducts 15 miles from the day, regardless of the number of trips / distance.

When the folks log their expenses, as usual it will be a row per trip, I want to be able to find a single row per date and deduct the 15 miles. Ideally from the first row.

Please see the snip below. Any help would be great.

To make the Daily Deduction col be checked I'm using the formula below:

=IF(COUNTIFS([Trip Date]:[Trip Date], [Trip Date]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row) = 1, 1)

I want to add to the IF to check if the Home Based col is "Y", but I'm getting unparseable.

If I could then combine it with the Formula in the Claimed Business Miles col, that would be great. The formula is:

=IF([Daily Deduction]@row = 1, ([Business Miles]@row - [Deducted Miles]@row), [Business Miles]@row)

As ever any help would be great.

AC

SteyJ

Update your daily reduction column to this:

=IF(AND(COUNTIFS([Trip Date]:[Trip Date], [Trip Date]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row) = 1, [Home Based]@row = "Y"), 1, 0)

A_C

Thanks very much for the solution @SteyJ, sorry for the delayed reply. I'm back on this bit of work now. I have updated the formula a little but I'm getting an unexpected outcome.

I now have a formula:

=IF(AND(COUNTIFS([Date Of Expense]:[Date Of Expense], [Date Of Expense]@row, [Expense Category]:[Expense Category], @cell = "Mileage", [Row ID]:[Row ID], @cell <= [Row ID]@row) = 1, [HC-Home Based]@row = "Y"), 1, 0)

This seems to work, please see below.

If I have a row below the first mileage row, with the same date, then the formula checks the Daily Deduction box for that row. Please see below.

Am I missing something in the formula? Would an INDEX/Match be better?

Thanks,

AC

Kelly Moore

Hey @A_C

Try one more addition to your IF/AND statement

=IF(AND(COUNTIFS([Expense Category]:[Expense Category], @cell = "Mileage", [Date of Expense]:[Date of Expense], [Date of Expense]@row, [Row ID]:[Row ID], @cell <= [Row ID]@row) = 1, [HC-Home Based]@row = "Y", [Expense Category]@row = "Mileage"), 1)

Will this work for you?

Kelly