Create a sheet that lists all working days in the date column

Album

I am creating a template for a daily log for our job sites. I want to find a quick way to fill in our working days into the rows so I'm not manually typing or clicking and dragging and deleting every weekend day, does anyone have a formula for making this happen?

Jeff Reisman

@Album This formula will cut out Saturdays and Sundays. Start by setting your first date in Row 1 manually, then below it enter:

=IF(WEEKDAY(Date1) = 6, Date1 + 3, IF(WEEKDAY(Date1) = 7, Date1 + 2, Date1 + 1))

Then just Ctrl-C/Ctrl-V this formula down through 260 or so more rows and you'll have all the week days for a year.

The logic is: If the day of the week for the date in the row above this one equals 6, aka Friday, add 3 days to that date; but if the day of the week for the date in the row above this one equals 7, aka Saturday, add 2 days to that date; otherwise, just add 1 to that date.

Jeff Reisman

@Album This formula will cut out Saturdays and Sundays. Start by setting your first date in Row 1 manually, then below it enter:

=IF(WEEKDAY(Date1) = 6, Date1 + 3, IF(WEEKDAY(Date1) = 7, Date1 + 2, Date1 + 1))

Then just Ctrl-C/Ctrl-V this formula down through 260 or so more rows and you'll have all the week days for a year.

The logic is: If the day of the week for the date in the row above this one equals 6, aka Friday, add 3 days to that date; but if the day of the week for the date in the row above this one equals 7, aka Saturday, add 2 days to that date; otherwise, just add 1 to that date.

Album

Awesome thanks! That worked perfectly.

Shellee Gillies

Had to reread the part about entered the 1st date manually… and then formula worked perfect! So helpful, thank you.