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

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?

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman Community Champion
    Answer ✓

    @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.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman Community Champion
    Answer ✓

    @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.


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Album
    Album ✭✭✭

    Awesome thanks! That worked perfectly.

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!