If then logic with dates

Ask for Help
1

My Expense Tracking app provides the ability to enter personal mileage. The user simply selects a customer code from a choice listing, enters the number of miles driven and the date upon which the mileage was driven.

Reimbursable mileage rates, per US Government rules, can change every year. The rate for 2020 was 57.5 cents per mile. This year it is 56 cents per mile. I have named mileage rate cells in a Parameters sheet. The rate of $0.575 is named MileageRate1. This year’s rate ($0.56) is named MileageRate2, etc.

I’d like to use If…Then logic to calculate the reimbursable dollar amount to the effect of: If ExpenseDate is greater than 12/31/20, use MileageRate2, otherwise MileageRate1. That way all 2020 entries will be preserved at the previous rate and this year’s amounts will also be accurate.

Suggestions on how to do this? Should it be done in the Google Sheet side or the Glide Data Editor?

2

Definitely in the Glide Data Editor.

Check out the if-then-else column. The configuration for that will be essentially as you described it.

3

Might be easier to just create a math column that pulls the year out of the date. YEAR(date). Then use that year in a relation to your mileage rate table which would have a year column and a rate column and a row for each year.

1 Like
4

I tried that to begin with, but the Then side of the equation would not let me use a specific value like 12/31/2021. Got another suggestion to use a function to pull the year into another column with a relation to a mileage table listing a Year column and Rate column and a row for each Year. Am going to try that. Thanks!

1 Like
5

Thanks Jeff. Will try that.

1 Like