CODEWITHSUNDEEP
excelexcel-formula
oryan5000
oryan5000

Reputation: 91

Excel Formula for finding if the 16th lands on a Saturday or Sunday and if it does, returning the date of the following Monday

Imagine that in cell A1 you have the first day of the month. I need a formula that finds if the 16th day of that month falls on a weekend (Saturday or Sunday) and if that is the case, return the date of the following Monday. If the 16th is not on a weekend, it will just return the date of the 16th

=If( (A1 + 15) = Saturday or Sunday, Date of the next Monday, (A1 + 15) )

I apologize in advance for my formula being terrible, I'm at a loss on this one. For example, if A1 = "10/1/2019" then the forula will return 10/16/2019 because the 16th is not a weekend. However, is A1 = "11/1/2019" then the formula will return 11/18/2019 because the 16th is a Saturday. Thanks!

Upvotes: 0

Views: 176

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

The workday function is probably the easiest way to do this:

=WORKDAY(A1+14,1)
  • A1+14 --> the fifteenth of the month
  • We then add one workday. If that added workday falls on a Sat or Sun (or optional holiday if you want to do that), the weekend days will be skipped, otherwise the 16th will be returned.

Upvotes: 1

Sorceri
Sorceri

Reputation: 8033

You can use the WEEKDAY function to determine the day of the week.

=IF(WEEKDAY(A1+15,2)=6,A1+17,IF(WEEKDAY(A1+15,2)=7,A1+16,A1+15))

Upvotes: 1

Related Questions