Help

Re: Date time challenge & Nested Ifs formula solved?

2287 0
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

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

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

Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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

image.png

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

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

Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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!

image.png

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!

Steve_Vo
6 - Interface Innovator
6 - Interface Innovator

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