Date time challenge & Nested Ifs formula solved?

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?

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

:slight_smile:

2 Likes

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

1 Like

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 :slight_smile:

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!

image

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?

image

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

2 Likes

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:

1 Like

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?

image

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