How to add a certain number of days to 'Date' type column and return a date?

djmalaka
Participant V

Hi,
I have a ‘date’ type column called [LMP]. I am trying to add 9 months and 7 days to the date in LMP column and return the resulting date to another column called [EDD]. Is it possible to do this? Since different months have a different number of days, what I want is for the app to figure out which month the LMP column has and based on that month, add 9 months and 7 days to that and bring out the date for the [EDD] column. Would appreciate your help on this.

Thanks.

0 10 769
10 REPLIES 10

Steve
Participant V

Try:

(EOMONTH([LMP], (9 - 1)) + DAY([LMP]) + 7)
  1. EOMONTH([LMP], (9 - 1)) gets the date of the last day of the month (9 - 1) = 8 months in the future.

  2. ... + DAY([LMP]) advances the date from (1) into the next month (the 9th month in the future), to the same day of that next month as the day of the month represented by [LWP].

  3. ... + 7 then advances the date from (2) an additional 7 days.

See also:

Hi @Steve,

As usual, very compact expression indeed.

Thank you so much! This is just brilliant Steve! An expressions god you are

By the way, instead of 9-1, can’t I put 8?

Yes, absolutely! I just made the math explicit to illustrate where the value comes from.

Thank you so much!

Hi @Steve,
Sorry to have opened up a previous thread, but I was wondering if I could “reverse engineer” this particular (EOMONTH([LMP], (9 - 1)) + DAY([LMP]) + 7) expression to go back in time.
See, here when we know the [LMP] date (LMP stands for last menstrual period date), your expression calculates the [EDD] (EDD means expected date of delivery of a pregnant mother). But if we know EDD, is it possible to calculate LMP by reducing 9 months and 7 days from the EDD?
I tried this
EOMONTH([EDD], -10) + DAY([EDD] - 7)

The thing is, it works most of the time, but when the DAY derived from EDD is less than 7 (let’s say EDD is 2021 October 4th), then instead of getting 2020 December 27th, I end up getting 2021 January 27th. The issue at hand seems to be due to the (- 7). Is there any other remedy to this?

Many thanks.

You tried:

EOMONTH([EDD], -10) + DAY([EDD] - 7)

Try this instead:

EOMONTH([EDD], -10) + DAY([EDD]) - 7

Oh. Thank you very much @Steve. The dates are correct now. If you don’t mind me asking, what goes wrong there?

You had a parenthesis in the wrong place. You had:

DAY([EDD] - 7)

which gives the day of the month (DAY(...)) of the date seven days prior to the EDD ([EDD] - 7). If the EDD is, for instance, March 10, seven days prior is March 3, so the result (DAY("3/3/2021")) would be 3. If EDD is March 2, seven days prior is February 23, so the result (DAY("2/23/2021")) would be 23. Whatever that result is then gets added to the date of the last day of the month 10 months prior to the EDD (EOMONTH([EDD], -10), which is May 31), giving either June 2 ("5/31" + 3) or June 22 ("5/31" + 23).

The correction:

DAY([EDD]) - 7

For March 10 gives (10 - 7) = 3, and for March 2 gives (2 - 7) = -5. Add those to May 31 gives either June 2 or May 26.

If my math is correct.

Top Labels in this Space