1

Is it possible to pre-populate a date in a column after date in adjacent column is entered?

Here is what I have been tasked with:

If date is on or before (<=) the 6th of the month (e.g. 01/01/2019) then the pre-populated date should be 21st of the following month (+1 month) (e.g. 21/02/2019)

If date is after (>) the 6th of the month (e.g. 07/01/2019) then the pre-populated date should be 21st +2 months (e.g. 21/03/2019)

The pre-populated will always be start with the 21st day of the month.

Any help with this is greatly appreciated

Many thanks

RT

4

This is pretty straightforward using the date functions.

enter image description here

Note that I'm in the US, so my dates are MM/DD/YYYY. I've added a December date so you can see how Excel intelligently handles the year if the month goes beyond 12. The formula in B1:

=DATE(YEAR(A1),MONTH(A1)+IF(DAY(A1)>6,2,1),21)

DATE() lets you specify a date by entering the year, month, and day. The target day is always the 21st day of the month. The year is taken as the year of the source date. All of the action happens with the month specification. We start with the month of the source date, then add either 1 or 2 months based on whether the source day is greater than 6. The logic is very human readable and follows what you described in the question.

  • More elegant than mine. – Mark Fitzgerald Jan 23 at 12:38
  • @MarkFitzgerald, great minds think alike. :-) Our formulas are almost the same and it looks like we were both writing answers at the same time. – fixer1234 Jan 23 at 12:41
1

If your date was in Cell A1 then this formula in any other cell in row 1 will work:

=IF(DAY(A1)>6,DATE(YEAR(A1),MONTH(A1)+2,21),DATE(YEAR(A1),MONTH(A1)+1,21))

Copy down as required.

1

I prefer the IF function as its clearer to read and understand what is going on, but as an alternative you could use the following:

=DATE(YEAR(A1),MONTH(A1)+1+(DAY(A1)>6),21)

The (DAY(A1)>6) will return either TRUE or FALSE. When this is sent through a math operation, in this case addition, it will be converted to 1 for TRUE and 0 for FALSE.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.