We have a couple of processes that calculate mileage using the Federal rate. Previously the rate had been hard-coded as a default value. When we were alerted several months ago that the rate would change for travel on or after 7/1/2022, I removed the hard-coded value and created a simple formula for that would apply the proper rate depending on a return date (=IF(Return_Date_1<"7/1/2022",0.585,0.625)). All was working fine until 10/1/2022 when the formula stopped working - after that date it will only return the 0.585 rate, not the needed 0.625 rate. FYI, the Return_Date_1 field is a text field that the process creator used to extract a date from another date/time field. I tried creating a different Return_Date field by extracting the date into a date field instead of a text field, but that didn't work either. There's probably something simple that I am missing, but it's eluding me. Thanks in advance.
Question
Question
Answer
Don't use strings or text fields for date comparisons. Like Brian said, they don't behave the way you want because it's not evaluating it in the proper context.
You were on the right track when you tried to extract the return date into a date field; you should definitely do that, but you also need to address the comparison value.
You'll want to use the DATE formula and compare it to an actual date type field.
Here's a working example of one that I use.
IF(Date_Received<DATE(2021,9,1),0,2.00)
The format is DATE(year,month,day)
Replies
Strings compare lexicographical order, where "10" < "7". The common suggestion to zero-pad your strings can help a little here, since "07" will compare less than "10". But you will run into problems when the years don't match - if the string order can be determined by looking at the beginning of the string (the month), later parts of the string will be ignored (the year). That is, "01/01/2023" < "07/01/2022". The best solution is to use date fields - what problems did you run into there?
To me it looks like your formula is evaluating if Return_Date_1 is less than 7/1/2022. I think you need to edit it so that its evaluating greater than. (=IF(Return_Date_1>"7/1/2022",0.585,0.625))
Thanks for taking the time to reply, but that's not it (and I did try it). If the return date is before 7/1/2022 it displays 0.585 as the rate (obviously in testing at this point), else/after that date then display 0.625. The thing is it worked properly up to 9/30/2022 so 10/1/2022 and after is throwing it off (i.e., it displays 0.585 regardless).
String comparisons have been the downfall of all of us at one time or another.
Thanks to all for your help and instruction.