Finding the first item in a day.
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
Answers
-
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)
Sincerely,
Jacob Stey
-
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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!