Help

Date time challenge & Nested Ifs formula solved?

Topic Labels: Formulas
6015 29
cancel
Showing results for 
Search instead for 
Did you mean: 
Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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?

29 Replies 29
AlliAlosa
10 - Mercury
10 - Mercury

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
6 - Interface Innovator
6 - Interface Innovator

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

Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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 :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
6 - Interface Innovator
6 - Interface Innovator

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

Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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

Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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