Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Value of <= with dates!

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1546
2

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 03, 2021 10:08 AM

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.

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 03, 2021 11:12 AM

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.

Reply

2 Replies 2

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 03, 2021 11:12 AM

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.

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Aug 03, 2021 11:19 AM

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!!