Date time challenge & Nested Ifs formula solved?

Try this:

First day of month:

DATETIME_PARSE(MONTH(TODAY()) & "/1/" & YEAR(TODAY()), 'M/D/YYYY')

Last day of month:

DATEADD(DATEADD(DATETIME_PARSE(MONTH(TODAY()) & "/1/" & YEAR(TODAY()), 'M/D/YYYY'), 1, 'month'), -1, 'day')

You are an Airtable Jedi, it works! Thanks a million!

Hi! Need a 2nd part to this formula to make it useful.

I have 3 Fields in my Airtable View.

1. Beginning of month


2. End of month (using your formula and they work perfectly, thanks!)


3. Deal date

This is what I would like to do. If deal date equal to or after beginning of month and equal to or before end of month, return 1.

How would you write the formula?

Hi,

I have found a formula that solved the challenge that I had. The below formula will return a 1 for any record date from beginning of the current month to the end of the current month.

DATETIME_FORMAT(TODAY(),“YYYYMM”)=DATETIME_FORMAT({Field Date Name},“YYYYMM”)

But now another challenge, does anyone know how to tweak it and make the formula works for PREVIOUS month?

The following should return 1 for anything with a {Deal Date} from the previous month

IF({Deal Date}, DATETIME_FORMAT(DATEADD({Month Start}, -1, 'month'), 'YYYYMM') = DATETIME_FORMAT({Deal Date}, 'YYYYMM'))

Not sure what your end goal is, but if you’re trying to create a field that will tell you whether the deal was from this month or last month, you can combine the formulas into something like this:

IF({Deal Date}, IF(DATETIME_FORMAT({Deal Date}, 'YYYYMM') = DATETIME_FORMAT({Month Start}, 'YYYYMM'), "This Month", IF(DATETIME_FORMAT(DATEADD({Month Start}, -1, 'month'), 'YYYYMM') = DATETIME_FORMAT({Deal Date}, 'YYYYMM'), "Last Month")))

Hope that helps!

You read my mind, it’s exactly what I want to do. You’re a Genius, thanks 2 million!

Hi!

I Just tried the formula but something is not right, it doesn’t works. Anything else need to be tweak?

Please see screen shot!

Hi,

Just letting you know that your formula works perfectly, I just need to add a {Today Field} and everything works, thank you so much!

IF({Deal Date}, IF(DATETIME_FORMAT({Deal Date}, ‘YYYYMM’) = DATETIME_FORMAT({Today}, ‘YYYYMM’), “This Month”, IF(DATETIME_FORMAT(DATEADD({Today}, -1, ‘month’), ‘YYYYMM’) = DATETIME_FORMAT({Deal Date}, ‘YYYYMM’), “Last Month”)))

Hi,

The formula works perfectly but I get some errors cell because the deal date has yet been entered. How can you tweak the formula to make the #ERROR! cells to shows 0?

Try adding a 0 to the outer false condition like below. Also, you can probably replace {Today} with the TODAY() function to eliminate an extra field.

IF({Start Date}, IF(DATETIME_FORMAT({Deal Date}, 'YYYYMM')=DATETIME_FORMAT(TODAY(), 'YYYYMM'), 'This Month', IF(DATETIME_FORMAT({Deal Date}, 'YYYYMM')=DATETIME_FORMAT(DATEADD(TODAY(), -1, 'month'), 'YYYYMM'), 'Last Month')), 0)

That last tweak fixed everything, all working perfectly. Thanks!

Hi,

This formula is working but I get an #ERROR! when the service booking date is blank. How would you tweak it to get rid of the #ERROR!?

All those nested ifs testing what the month is could be a singular Switch function: SWITCH({Month},1,'January',2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',10,'October',11,'November',12,'December')

To run a formula only when a field is not blank you would do something like this: IF({Field in question}, <>, BLANK()). In your case, you’d be inserting the SWITCH function in place of the ‘<>’.

Thanks for your help, no more #ERROR!, cheers!

Hi,

Can you help solve this challenge? I have a multi-select field and whenever there is a CANP-AL or CANP-GL, I would like it to be input in a separate field? Is this possible? How would you write the formula? Thanks in advance!

Use the FIND() function.

IF(FIND('CANP-AL',{Delivery Co-ordinator Activities}),'CANP-AL',IF(FIND('CANP-GL',{Delivery Co-ordinator Activities}),'CANP-GL'))

For future reference please refer to Airtable’s formula function definition page:

Works like Magic, thank you so much!

Hi,

Just need a little tweak in your formula. The formula is working but only returns one activity. How would get it to returns multiple activities when performed by one person?

Your original request said “or”, so the formula works as an or statement. For it to work for both cases, make two IF() statements seperated by an &. IF(condition, 'value if true')&IF(condition, 'value if true')

Hi,

I have revised the formula with an extra & . It’s not working, What’s missing?

IF(FIND(‘App Set-AL’,{Delivery Co-ordinator Activities}),‘App Set-AL’,&IF(FIND(‘Books Done-AL’,{Delivery Co-ordinator Activities}),‘Books Done-AL’,&IF(FIND(‘Veh Checked-AL’,{Delivery Co-ordinator Activities}),‘Veh Checked-AL’,&IF(FIND(‘Delivered-AL’,{Delivery Co-ordinator Activities}),‘Delivered-AL’,&IF(FIND(‘MotorOne-AL’,{Delivery Co-ordinator Activities}),‘MotorOne-AL’,&IF(FIND(‘CANP-AL’,{Delivery Co-ordinator Activities}),‘CANP-AL’,&IF(FIND(‘A/M on Del-AL’,{Delivery Co-ordinator Activities}),‘A/M on Del-AL’,&IF(FIND(‘MyToyota-AL’,{Delivery Co-ordinator Activities}),‘MyToyota-AL’))))))))

You using several nested if statements instead of two or more separate if statements separated by an ampersand.

Hi,

I’m not sure how to write the formula to make it work? Can you please tweak it?

IF(condition, ‘value if true’)&IF(condition, ‘value if true’)

IF({Delivery Co-ordinator Activities},‘App Set-AL’,‘Delivered-AL’)&IF({Delivery Co-ordinator Activities},‘App Set-AL’,‘Delivered-AL’)

The way you’ve written it, by putting only the field name into the “condition” part of the IF function, it will check to see if anything is in the {Delivery Co-ordinator Activities} field. The “condition” part should be the full FIND syntax you were using before, looking for specific text, and outputting that text if found (the “value if true”).

Long story short, the change you need to make to your tweaked version is to close each IF before the next IF begins, instead of nesting them inside each other. End the IF with the closing parenthesis, add an & to concatenate that IF’s result with the next one, then write the next IF as a standalone piece. Lather, rinse, and repeat. Here’s the beginning restructured in that way:

IF(FIND('App Set-AL',{Delivery Co-ordinator Activities}),'App Set-AL')

& IF(FIND('Books Done-AL',{Delivery Co-ordinator Activities}),'Books Done-AL')

& IF(FIND('Veh Checked-AL',{Delivery Co-ordinator Activities}),'Veh Checked-AL')

...

Does that make the setup more clear?

Follow @Justin_Barrett’s example.

This post no longer has anything to do with its title. Can you rename it to reflect the 4 or so rounds of questions? This will make it easier for other users if they have similar questions to find the answer.

Hi!

Just want to report that’s everything is working like a Swiss Watch, thanks everyone!