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

Options

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 ✭✭✭✭✭✭
    Answer ✓
    Options

    @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 ✭✭✭✭✭✭
    Answer ✓
    Options

    @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 ✭✭✭
    Options

    Awesome thanks! That worked perfectly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!