Skip to main content

Hi Airtable Community!



Need some assistance for a date formula that is stumping me. Here is the backstory and the info that accompanies the screen shot I have attached:



I have a field that the user chooses for a month called “S1 Month – Choose”. I then have a field next to it called “S1 Month Shown”. This field converts the 1st field to a numeric value using the following formula:



IF({S1 Month - Choose}=“January”,“1”,IF({S1 Month - Choose}=“February”,“2”,IF({S1 Month - Choose}=“March”,“3”,IF({S1 Month - Choose}=“April”,“4”,IF({S1 Month - Choose}=“May”,“5”,IF({S1 Month - Choose}=“June”,“6”,IF({S1 Month - Choose}=“July”,“7”,IF({S1 Month - Choose}=“August”,“8”,IF({S1 Month - Choose}=“September”,“9”,IF({S1 Month - Choose}=“October”,“10”,IF({S1 Month - Choose}=“November”,“11”,IF({S1 Month - Choose}=“December”,“12”))))))))))))



I have another field that is being used in this process called “Today” that is a formula field to generate the date of today. Here is the formula I am using for this:


NOW()



Next to that is another field named “Date Format for Today Field”. This is a formula I am using to convert the “Today” field into a number. I am using the formula:


DATETIME_FORMAT(Today,“M”)



I then have another field called “Storage 1 $” that is a user input currency field. Next to that field is another one called “% Compl Storage 1”. All of the above stuff is to auto-fill this field with one of two variables. IF the month is either before or equal to Today’s month THEN put 100% into the field. If it is not, then put 0%. The idea here is that if we are in the current month or the past I want to call it as 100% done. If we are in the future, then nothing is done (i.e. 0%). We have a lot of jobs where we invoice the storage part months in advance so this is why we need this. In fact, there are a total of 18 months I need to do this on. This example is just for the 1st month. Here is the formula I am using:


IF({S1 Month Shown}<={Date Format for Today Field},“100%”,“0%”)



Now – the part that is NOT working! When I choose the dates in the “S1 Month – Choose” it works for every month as it is supposed to with the exception of October, November, and December. I can only assume that this has something to do with the 10, 11, 12 numbers and somehow the formula I am using above is not recognizing that 10, 11, and 12 are larger than 8. I think it is seeing the 1 in front, but that is just a guess.



So this is where I am stuck and not sure how to get this to work……



By the way – the reason for all the workarounds and conversions is I could not get it to work at all when I was using date month words (August, September, etc.) instead of numbers (8, 9, etc.). I am sure there is a better way to get to where I am trying to go, but here is where I find myself!



Any help would be greatly appreciated.



You’re performing math (<=) on strings, not numbers. By placing the numerical value of each month in quotation marks, you are outputting a string instead of a number. That’s why {S1 Month Shown} is aligned to the left of the cell and not to the right like an actual number would. Because “10” is “alphabetically” before “8”, your formula will not calculate properly for any current month apart from January.



Also, to simplify your first formula down, you can use a single SWITCH() instead of 12 IF()s.



SWITCH(


   {S1 Month - Choose},


   "January", 1,


   "February", 2,


   "March", 3,


   "April", 4,


   "May", 5,


   "June", 6,


   "July", 7,


   "August", 8,


   "September", 9,


   "October", 10,


   "November", 11,


   "December", 12


)


Similarly, your DATETIME_FORMAT() is outputting a string. You could wrap the formula in a VALUE() function, or simplify the formula to:



MONTH({Today})


And once more if you need your output to be usable percentages, then your final formula would be:



IF({S1 Month Shown}<={Date Format for Today Field}, 1, 0)


and the field would be formatted as a percentage. If you’re fine with the output being a string here, keep “100%” and “0%” as you have them.

You’re performing math (<=) on strings, not numbers. By placing the numerical value of each month in quotation marks, you are outputting a string instead of a number. That’s why {S1 Month Shown} is aligned to the left of the cell and not to the right like an actual number would. Because “10” is “alphabetically” before “8”, your formula will not calculate properly for any current month apart from January.



Also, to simplify your first formula down, you can use a single SWITCH() instead of 12 IF()s.



SWITCH(


   {S1 Month - Choose},


   "January", 1,


   "February", 2,


   "March", 3,


   "April", 4,


   "May", 5,


   "June", 6,


   "July", 7,


   "August", 8,


   "September", 9,


   "October", 10,


   "November", 11,


   "December", 12


)


Similarly, your DATETIME_FORMAT() is outputting a string. You could wrap the formula in a VALUE() function, or simplify the formula to:



MONTH({Today})


And once more if you need your output to be usable percentages, then your final formula would be:



IF({S1 Month Shown}<={Date Format for Today Field}, 1, 0)


and the field would be formatted as a percentage. If you’re fine with the output being a string here, keep “100%” and “0%” as you have them.


So incredibly helpful! Thank you for showing me all of the solutions. Everything you suggested works perfectly!



I totally get what I didn’t understand. Very much appreciated!!

Reply


Terms & Conditions