Help

Re: Pay schedule calculating dates

Solved
Jump to Solution
1166 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Erik_Peterson
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

Screenshot 2022-08-25 at 2.44.41 PM

See Solution in Thread

5 Replies 5
TheTimeSavingCo
17 - Neptune
17 - Neptune

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

Screenshot 2022-08-25 at 2.44.41 PM

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

Erik_Peterson
5 - Automation Enthusiast
5 - Automation Enthusiast

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.
Screen Shot 2022-08-25 at 10.11.05 AM

“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.

oh duh! Thanks so much!