Date time challenge & Nested Ifs formula solved?

[Steve_Vo]

How would you write a date time formula for Beginning of current month to End of current month? The trick is, it’s the current month and the month always changes.

Any Jedi out there?

[Kamille_Parks]

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)

[Steve_Vo]

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

[Steve_Vo]

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

[Kamille_Parks]

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 ‘<>’.

[Steve_Vo]

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

[Steve_Vo]

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!

[Kamille_Parks]

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:

Airtable - Support

Formula field reference

For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields. Formulas may involve functions, numeric operations, logical operations, and text operation...

[Steve_Vo]

Works like Magic, thank you so much!

[Steve_Vo]

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?

[Kamille_Parks]

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')