Skip to main content

Convert a date to the 20th day of the FOLLOWING month

  • September 24, 2018
  • 2 replies
  • 0 views

Forum|alt.badge.img+3

I need to take (order date), and convert it to (invoice due date) that is equal to the 20th of the following month. I’ve tried a bunch of things, but to no avail. Any help greatly appreciated :slightly_smiling_face:

2 replies

Forum|alt.badge.img+18

Assuming you ALWAYS want the 20th of the following month, regardless of the {Order Date}:

IF({Order Date},
   DATETIME_PARSE(
      SWITCH(
         MONTH({Order Date}),
         12, 1,
         MONTH({Order Date}) + 1
      )
      & "/"
      & "20"
      & "/"
      & SWITCH(
            MONTH({Order Date}),
            12, YEAR({Order Date}) + 1,
            YEAR({Order Date})
         )
   )
)

and then adjust your formatting settings to your liking:

(I’d suggest keeping “Use the same time zone…” checked to avoid values being off by one day)


Forum|alt.badge.img+3
  • Author
  • Known Participant
  • 25 replies
  • September 25, 2018
Jeremy_Oglesby wrote:

Assuming you ALWAYS want the 20th of the following month, regardless of the {Order Date}:

IF({Order Date},
   DATETIME_PARSE(
      SWITCH(
         MONTH({Order Date}),
         12, 1,
         MONTH({Order Date}) + 1
      )
      & "/"
      & "20"
      & "/"
      & SWITCH(
            MONTH({Order Date}),
            12, YEAR({Order Date}) + 1,
            YEAR({Order Date})
         )
   )
)

and then adjust your formatting settings to your liking:

(I’d suggest keeping “Use the same time zone…” checked to avoid values being off by one day)


Jeremy - you are a star!
This works like a charm - let me buy you a beer, when you come and visit!


Reply