Search code examples
if-statementexcel-formulaweekday

Setting up a condition to verify if the 20th day of the month exist then do something, otherwise go to next workday

I have formulated workdays for a month and need to display in the column next to those days data conditional to, for this instance:

if the day is the 20th, i.e: Day(Cell) = 20, do the thing on the 20th.

Some months though do not have a 20th, so I want the condition to evaluate if the 20th exists, else go to next workday and do what it was supposed to do on the 20th.

Current formula used is :

=IF(DAY(A16)=20;"do the thing on the 20th";"")

in the snapshot, April 2019 does not have a 20th of the month and therefore nothing is displayed in column C, I would want it to be displayed on the next workday which would be the 22nd of April.

snapshot of current result since no 20th exist

Solution

  • To shift Sat, Apr 20, 2019 to Mon, Apr 22, 2019 subtract a day and add a workday.

    =IF(A16=WORKDAY(DATE(YEAR(A16); MONTH(A16); 19); 1); "do the thing on the 20th"; "")

    If you don't like the DATE(YEAR(A16); MONTH(A16); 19) date construction, EOMONTH(A16, -1)+19 accomplishes the same thing; i.e. the 19th day of the month and year in A16.