Formula to autofill a date series that shows every Monday & first day of month

I am trying to create a sheet to track hours by week. I need a formula to calculate the first day of the week and the first day of the month if the first day of the month occurs between Mon-Fri. In Excel I'm able to accomplish this with:

=IF(WEEKDAY(B2) <> 2, A2+ 7, 

 IF(OR=MONTH(B2+7) > MONTH(B2), YEAR(B2+7) > YEAR(B2)), 

 IF(OR(WEEKDAY(EOMONTH(B2, 0) + 1) = 1, 

 WEEKDAY(EOMONTH(B2, 0) + 1) = 7), B2+1, EOMONTH(B2, 0) + 1), B2+7))

but without EOMONTH in smartsheet I'm lost.

Appreciate any guidance you might have -



  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 08/28/22

    Hey @Christine Gray

    Not knowing exactly how you are using your data, I wondered if could you track your Weeks using WeekNumber instead of Weekday? WeekNumber would allow you to group the dates by this number and WeekNumber begins on Mondays. If yes, it might simplify your equation.

    I find the End of Month by knowing that it is the Day 1 of the Next Month, minus 1. =DATE(YEAR(your date]@row, MONTH([your date]@row)+1, 1)-1. The watchout is Dec 31 because the year changes.

    Sorry I didn't follow your formula well enough to drop this in. It doesn't seem like your Weekday portion calculates Mondays, which I thought you were trying to do. I would have used IF(WEEKDAY(Date@row)<>2, 7-Weekday(Date@row)+Date@row, Date@row) *This assumes non of your dates are Sundays - there's another condition for that

    I hope the above gets you closer. If you haven't found the smartsheet function list yet you can find it here:


  • Brent Wilson
    Brent Wilson ✭✭✭✭✭

    A Little Trick is DATE(YEAR(Date@row), MONTH(Date@row) + 1) will always return on the First of the next month

    Or DATE(YEAR(Date@row), MONTH(Date@row) + 1) -1 will return the Last Date of the This Month

    SMARTSHEET fixes the year error so it is mart enough to make Dec + 1 Month = Jan + Year+1

    Try this

    =IF(MONTH(Date@row + (9-WEEKDAY(Date@row))) > Month(Date@row), IF(WEEKDAY(Date@row) >= 2 , DATE(YEAR(Date@row), MONTH(Date@row) + 1), IF( WEEKDAY(Date@row) = 1, Date@row + 1, DATE(YEAR(Date@row), MONTH(Date@row) + 1))), Date@row + (9-WEEKDAY(Date@row)))

    Brent C. Wilson, P.Eng, PMP, Prince2

    Facilityy Professional Services Inc.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!