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

Christine

• ✭✭✭✭✭✭
edited 08/28/22

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

• ✭✭✭✭✭

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.

http://www.facilityy.com

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!