MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dates - excluding weekends


Posted by Bob McDonald on January 15, 2001 12:49 PM

Hello,

Is there a way to fill cells with dates excluding weekends?

thanks

Bob


Posted by Ronda on January 15, 2001 1:05 PM


I put the date in one cell(A1), then next cell(B1) {=A1+1}. I fill right then go through and change the Monday references to {=prev_cell+3}. For use in another place, I copy this string and paste, so it only has to be done once, or "save as" the workbook with a different name. The next year I can use same format with minor revisions for leap year, etc. Another way that sometimes works for me is to fill right with the {=A1+1} formula, then hide the weekend rows. This helps to ease the reusage of the formula in the future. These are fairly elementary ideas that I use. I am sure someone has a better solution.

Posted by jtf on January 15, 2001 2:18 PM


Response: Use Excel's Workday function. Place your first date in A1 then place the following formula in A2 WORKDAY(A1,1) then copy down til you have the desired amount of dates excluding weekends.

Posted by Thom Moon on January 18, 2001 1:30 AM

type the start day and right click and drag the from the bottom right of the cell to roughly the required date, let go and excel with give you a few options to fill with, just select "fill weekdays" (beware of public holidays)