Mar 06, 2019 08:52 AM
If I want to specify the last day of the current month in a cell as a date, what is the syntax? In Google Sheets & MS Excel it is EOMONTH (End Of Month).
EDIT - HERE IS THE FORMULA - THANKS KAMILLE & JUSTIN
DATETIME_FORMAT(
DATEADD(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
'YYYY'
) & '-' &
(VALUE(
DATETIME_FORMAT(
TODAY(),
'MM'
)
)
+ 1)
& '-01',
'YYYY-MM-DD'
) , -1, 'day'
)
, "MM-DD-YY"
)
Solved! Go to Solution.
Jul 07, 2019 10:05 PM
When using this formula Airtable does not seem to read this as a ‘date’ making it difficult to use the End of Month date in other formulas.
I found using the below formula to be easier and enables the use of the date in other formulas, such as findind how many days left until the end of the month.
Formula: DATEADD(DATEADD({Start Date},1,‘month’),-DAY(DATEADD({Start Date},1,‘month’)),‘days’)
If my start date is ‘01/01/2019’ then the formula will result in ‘31/01/2019’ and will pick this up in date format.
If you are wanting the start of a month, then I use this formula:
DATEADD(DATEADD({End Date},1,‘days’),-DAY({End Date}),‘days’)
Hope this is helpful :slightly_smiling_face:
Mar 06, 2019 01:14 PM
There is probable a more elegant way to do this, but you can try this formula:
DATETIME_FORMAT(DATEADD(DATETIME_FORMAT(DATETIME_FORMAT(TODAY(),'YYYY')&'-'&DATETIME_FORMAT(TODAY(),'MM')+1&'-01','YYYY-MM-DD'),-1,'day'),'DD')
Mar 07, 2019 08:42 AM
Thank You Kamille. This provided the number 31. Which technically speaking is what I asked. I will tweak this code to return the EOMONTH as a date. So, rather than 31, it will return 03-31-2019. Thanks for taking the time to put together that formula, it’s much appreciated.
Also - note to @Airtable, please consider adding EOMONTH functionality. As you can see it is quite complicated to write this as a function.
Mar 07, 2019 09:01 AM
If you remove the outer date time format portion of the formula you should get your results as a full date as opposed to just the day.
Mar 08, 2019 10:28 AM
DATEADD(DATETIME_FORMAT(DATETIME_FORMAT(TODAY(),‘YYYY’)&’-’&DATETIME_FORMAT(TODAY(),‘MM’)+1&’-01’,‘YYYY-MM-DD’),-1,‘day’) - resulted in: 12/30/2000
DATETIME_FORMAT(DATETIME_FORMAT(TODAY(),‘YYYY’)&’-’&DATETIME_FORMAT(TODAY(),‘MM’)+1&’-01’,‘YYYY-MM-DD’) - resulted in 2001-01-01
So, I am not sure that we are on the same page. :- :grinning_face_with_smiling_eyes:
Mar 08, 2019 04:51 PM
I messed with the formula a bit, and was able to get it working.
DATEADD(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
'YYYY'
) & '-' &
(VALUE(
DATETIME_FORMAT(
TODAY(),
'MM'
)
)
+ 1)
& '-01',
'YYYY-MM-DD'
) , -1, 'day'
)
Mar 12, 2019 02:19 PM
Thank You Justin. For me your formula returned 3/30/2019, which is close enough for what I am trying to do.
I still recommend to AirTable to place End of Month into the date options. Thanks Again Justin.
Mar 12, 2019 03:17 PM
Sorry. I overlooked the formatting you’d requested. That’s easily addressed by wrapping DATETIME_FORMAT around the above. Here’s the full formula with that addition:
DATETIME_FORMAT(
DATEADD(
DATETIME_PARSE(
DATETIME_FORMAT(
TODAY(),
'YYYY'
) & '-' &
(VALUE(
DATETIME_FORMAT(
TODAY(),
'MM'
)
)
+ 1)
& '-01',
'YYYY-MM-DD'
) , -1, 'day'
)
, "MM-DD-YY"
)
Jul 07, 2019 10:05 PM
When using this formula Airtable does not seem to read this as a ‘date’ making it difficult to use the End of Month date in other formulas.
I found using the below formula to be easier and enables the use of the date in other formulas, such as findind how many days left until the end of the month.
Formula: DATEADD(DATEADD({Start Date},1,‘month’),-DAY(DATEADD({Start Date},1,‘month’)),‘days’)
If my start date is ‘01/01/2019’ then the formula will result in ‘31/01/2019’ and will pick this up in date format.
If you are wanting the start of a month, then I use this formula:
DATEADD(DATEADD({End Date},1,‘days’),-DAY({End Date}),‘days’)
Hope this is helpful :slightly_smiling_face:
Jul 07, 2019 11:35 PM
Similarly, the formula I’ve been using in practice as of late is much more simple as well. Its much like the one you provided, but more general so it only requires one reference to another field:
DATEADD(DATEADD(DATETIME_FORMAT({Date in Question},'YYYY-MM-01'),1,'month'),-1,'day')
^ Nice try, Old Me. haha
Oct 03, 2019 04:50 AM
this post was helpful
[
May 15, 2021 01:15 PM
Great help, it works like magic. Thanks!
Feb 06, 2022 07:29 PM
This was really helpful!
Jun 27, 2022 08:17 AM
I realize this is kind of a dead thread, but wanted to share the solution I came up with. Requires no reference fields and is pretty short and elegant (I think!). Basically subtracts today’s day number from today’s date and adds a month, returning this month’s last day:
DATEADD((DATEADD(TODAY(), -DAY(TODAY()), ‘days’)), 1, ‘months’)
Jun 29, 2022 08:56 AM
I’m working on a tutorial for how to recreate an EOMONTH function and this is a brilliant way to simplify it, @Kamille_Parks . I love @Patrick_Bliss 's too, although if I wasn’t the creator and stumbled across both formulas, I think it would be easier to figure out what’s going on with the DATETIME_FORMAT way of doing it.
I had no idea that you can enter numbers into a DATETIME_FORMAT function, which is really nice and useful. Having a hard time wrapping my brain around why it works. Like, when you sub in ‘01’ for ‘DD’, Airtable doesn’t actually know that you want to replace ‘DD’, because you didn’t specify that anywhere… so it’s just making an educated guess?
Jun 29, 2022 09:37 AM
I actually think its ignoring the “01”. I have since learned that DATETIME_FORMAT({Date in Question},'YYYY-MM')
always resolves to the first of that month. Similarly, “YYYY” would resolve to Jan 1 of that year.
So the shortest formula you could write using my method would be
DATEADD(DATEADD(DATETIME_FORMAT({Date in Question},'YYYY-MM'),1,'month'),-1,'day')
Patrick’s solution works as well. I prefer DT Format because often times people need to adjust for timezone, which you can only do if you’re using the DT Format function anyway.
Jun 29, 2022 11:30 AM
That makes sense! Thanks :slightly_smiling_face:
Jul 10, 2022 01:22 AM
This is the perfect solution. Thanks @Kamille_Parks