Jul 13, 2019 03:33 PM
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?
Jul 13, 2019 09:02 PM
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:
Jul 13, 2019 09:40 PM
You are an Airtable Jedi, it works! Thanks a million!
Jul 13, 2019 11:01 PM
Hi! Need a 2nd part to this formula to make it useful.
I have 3 Fields in my Airtable View.
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?
Jul 14, 2019 02:34 AM
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?
Jul 14, 2019 09:48 AM
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!
Jul 14, 2019 03:01 PM
You read my mind, it’s exactly what I want to do. You’re a Genius, thanks 2 million!
Jul 14, 2019 04:23 PM
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!
Jul 15, 2019 06:30 AM
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”)))
Jul 15, 2019 09:50 PM
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?
Jul 15, 2019 11:02 PM
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)
Jul 16, 2019 12:44 AM
That last tweak fixed everything, all working perfectly. Thanks!
Jul 17, 2019 05:19 PM
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!?
Jul 17, 2019 07:04 PM
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 ‘<>’.
Jul 17, 2019 11:19 PM
Thanks for your help, no more #ERROR!, cheers!
Jul 17, 2019 11:53 PM
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!
Jul 18, 2019 08:56 AM
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:
Jul 18, 2019 04:39 PM
Works like Magic, thank you so much!
Jul 18, 2019 05:13 PM
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?
Jul 18, 2019 05:28 PM
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')