Formula calculation using date fields

[Erik_Peterson]

I was tring to modify a formula DATEADD({Start Date},13,‘days’) to find the end of the month but since months have differing amounts of days I wasn’t sure how I could do that. Bascially, I want the formula look at the start date and then output the last date in that month. So a start of 9/1/22 would equal 9/31/22, etc. Thanks in advance!

[TheTimeSavingCo · Accepted solution]

Hey @Erik_Peterson, this should do what you’re looking for I think:

DATEADD(
  DATEADD(
    DATETIME_PARSE(
      DATETIME_FORMAT(
        Date,
        "01 MM YYYY"
      ),
      "DD MM YYYY"
    ),
    1,
    'months'
  ),
  -1,
  'days'
)

[ScottWorld]

One key thing to note here is to leave GMT turned on in the formatting preferences for the formula field.

[Erik_Peterson]

ok this awesome, but I’ve got something incorrect b/c I’m getting an error. I do see you were using a euro format date and I’m using a US based date format. Even when I swap the DD & MM in the formula it doesn’t seem to work. See attached screenshot.

[ScottWorld]

“Start of Period” is a field name, so it needs to be in {curly brackets}.

That piece of information was missing from @Adam_TheTimeSavingCo’s formula above.

[Erik_Peterson]

oh duh! Thanks so much!