Help

error in formula to calculate month start and end date

Topic Labels: Dates & Timezones Formulas
215 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Neptune292
4 - Data Explorer
4 - Data Explorer

I have a table with work months like here:

Screenshot 2024-01-19 at 19.14.03.png

 

I'm trying to calculate the start and end datetimes of the month but the formula is giving inconsistent answers. It's correct for some months but there's an error in End Dates for March, May, July, October and December.

Here is the formula I'm using for Start and End dates:

Start Date:
DATEADD
(DATETIME_PARSE(YEAR({Work Month}) & "-" & MONTH({Work Month}) & "-01", 'YYYY-MM-DD'),-7,'hour')
 
End Date:
DATEADD(DATEADD({Start Date},1,'month'),-1,'second')

What am I doing wrong?

2 Replies 2
dilipborad
8 - Airtable Astronomer
8 - Airtable Astronomer

Hello @Neptune292,

If the formatting options and timezone don't matter for you then use these simple formulas. Like this. Then format the date using default formatting options or using DATETIME_PARSE

Start Date:

DATETIME_PARSE({Work Month})

End Date: Same as your's

DATEADD(DATEADD({StartDate},1,'month'),-1,'second')

The wrong you've done is you minus -7 hours from the current date. Which affects on the next field as well.

See this screenshot with or without that -7 hour.

dilipborad_0-1705672163386.png

I hope this helps.

👍

thanks for your response.

timezone matters for me. i'm trying to set the timezone to my timezone that's why I did the minus 7.

I don't know why the formula calculates it correctly for some months but not others

I was able to fix it by setting the End Date to this:

DATEADD(DATEADD(DATEADD(DATETIME_PARSE(YEAR({Work Month}) & "-" & MONTH({Work Month}) & "-01", 'YYYY-MM-DD'),1,'month'),-1,'second'),-7,'hour')