Recently we wanted a report to show when a piece of work was due to be completed based on populating a start date and the number of days work required. However, we needed to factor in that the work might span one or more weekends so needed to 'ignore' weekend days.

We thought this calculation might prove helpful for other users so it's shown below:

DATE_ADD(cf_start_date, INTERVAL ((cf_no_of_days_work DIV 5) * 7
+(cf_no_of_days_work % 5) + IF(((cf_no_of_days_work % 5)
+ WEEKDAY(cf_start_date)) IN (5,6,12,13), 2, 0)) DAY)

For our purposes, we knew that the work would never be scheduled to start on a Saturday or Sunday but we needed to incorporate into the formula a way to adjust the completed date if the work spanned any weekend days. NOTE: We haven't included any way to calculate whether or not public holidays are spanned but nevertheless, this formula gives us a much more reliable way to identify the completion date for pieces of work and hopefully you'll find it useful too.