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

Christine Gray

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 -

Christine

Kelly Moore

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: https://help.smartsheet.com/functions

Kelly

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)))