Add 6 exact months to an existing date

[Paulo]

Hello all! I'm trying to populate a date field [Final_Date] using the expression below:

EOMONTH([Start_Date],6) - EOMONTH([Start_Date],0) + [Start_Date]

 The ideia is to add exact 6 months to the [Start_Date] and it's working just fine when the start date is before February. (E.g.: When [Start_date] is 2022/01/17 the result is 2022/07/17) But when the [Start_Date] is after Ferbruary there's two or three days difference. (E.g.: When [Start_date] is 2022/02/07 the result is 2022/08/10 instead of 2022/08/07).

Could anyone help me or point out the right direction, please?

[Suvrutt_Gurjar]

I think these discrepancy will be there because of the way calendar system is there. There are some months with 31 days, some with 30 and February with 28 or 29 and finally leap year with 365 days. So days duration calculations do not produce exact results. Challenges are because date/ time does not follow exact decimal format.

You may need to decide your requirements more stringently and the expressions could also be built more stringently  accordingly but still there could be some exceptions.

It sounds from your examples that you are looking for exact same day in date  after 6 months. Please consider a case of  08/31/2020 after 6 months it should be 02/31/2021 but since February does not have 31 days you will need to settle for 02/28/2021 or 03/03/2021. 

Similarly 03/31/2022 + 6 months cannot become 09/31/2022 because September has 30 days. Result needs to be either 09/30/2022 0r 10/01/2022.

You may need to decide what you wish on such outlier cases and then possibly an expression could be built. But with date / time , especially date expressions , there could be always some exceptions that one may need to live with.

Edit : Please try the below expression. I think it will work majority cases except when start dates are such as is 08/31 ( 31st August) or 03/31 ( 31st March) 

EOMONTH([Start Date],5)+ DAY([Start Date])

[Steve · Accepted solution]

---

@Suvrutt_Gurjar wrote:

Please try the below expression. I think it will work majority cases except when start dates are such as is 08/31 ( 31st August) or 03/31 ( 31st March) 

EOMONTH([Start Date],5)+ DAY([Start Date])

---

MIN(
  LIST(
    (EOMONTH([Start Date], 5) + DAY([Start Date])),
    (EOMONTH([Start Date], 6) - 1)
  )
)

[Paulo]

@Suvrutt_Gurjar @Steve Thank you for the help!