I have a formula used to calculate expenses due for travel. It uses the field “miles travelled” minus 10 as I can’t claim the first 10 miles. So far so good but I need to change the 10 to 15 for future calculations.
If I edit the formula, my whole database of past records change. Can I change the formula to save the past calculations? I guess I want to lock the past records once they’re complete?
Thanks!
Hi David,
I’m sorry I missed replying to your post yesterday.
What you can do is use the IF function to return 10 if the Date value is less than a specific date and 15 if it’s not.
For example:
IF(Travel Date < DATEVALUE("2018-01-01"; "yyyy-MM-dd") ; Miles - 10; Miles - 15)
So that will check if you have a field called “Travel Date” that is before January 1, 2018. If so, the Miles field will be subtracted by 10. If the Travel Date is equal to or after January 1, 2010, 15 will be subtracted from your Miles field.
Hope that’s what you’re looking for.
Thanks!
Brendan
Thanks Brendan, that’s what I needed.
I should try and remember a database is just like a spreadsheet when it comes to calculations!
Thanks again!
Guess what, I need to update this again. So now I need two IFs? Something like
IF(Travel Date < DATEVALUE("2018-01-01"; "yyyy-MM-dd") ; Miles - 10; IF (Travel Date < DATEVALUE("2018-06-01"; "yyyy-MM-dd") ; Miles - 15 ; Miles - 20)
OTOH I now see a checkbox “Calculate one time only”. Would that help me?
Thanks!
Hi David,
“Calculate one time only” is only useful if you’re using the UUID() function to generate a unique random value in a field.
You’re missing a parenthesis on the right of your formula to close out the first IF statement.
Other than that it looks good to me.
Thanks!
Brendan
Yes, thanks, that works now. I seem to have to re-edit formula for other calculations which use my ‘miles traveled’ result. But I click ‘edit formula’ for those and just save it works perfectly again.
Brilliant!
You can also just click the Refresh button at the bottom of the records list view and that’ll recalculate all the formulas too without you having to re-edit the formulas.
.
