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?

[AlliAlosa]

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

:slightly_smiling_face:

[Steve_Vo]

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

[Steve_Vo]

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?

[Steve_Vo]

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?

[AlliAlosa]

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

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!

[Steve_Vo]

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

[Steve_Vo]

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!

[Steve_Vo]

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

[Steve_Vo]

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?