How to calculate the nth day of the month, or the preceeding weekday if nth day is on weekend.

David Bigelow
David Bigelow used Ask the Experts™
on
For Excel, what is a formula for calculating the 25th day of the month, or the weekday before the 25th if it falls on a weekend day? I'll be using a date reference cell of H4.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Formula valid for any date in cell H4:
=DATE(YEAR(H4),MONTH(H4),25)-(WEEKDAY(DATE(YEAR(H4),MONTH(H4),25),2)>5)-(WEEKDAY(DATE(YEAR(H4),MONTH(H4),25),2)=7)

Open in new window

Formula valid if date in H4 is the 25th of the month:
=H4-(WEEKDAY(H4,2)>5)-(WEEKDAY(H4,2)=7)

Open in new window

David BigelowStaff Operations Specialist

Author

Commented:
Hi byundt,
Thank you for your help. The first formula is exactly what I needed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial