# Week number based on the month of the created date?

✭✭✭

How would I calculate the week number based on the month of a created date for a record? For example, I want to know if the data collected was provided the 1st, 2nd, 3rd, 4th or 5th (if applicable) week of the month in which the data was provided.

Thanks!

• ✭✭✭✭✭

=ROUNDDOWN(([Created]@row - DATE(YEAR([Created]@row), MONTH([Created]@row), 1)) / 7, 0)

From the outside in

ROUND has to round it up or down I assume.. you can change the 0 to 1 to get 2.5 weeks versus say 2

I took the Date in the created row minus the 1st of the month (using the created row). You could hardcode the year or the month but I thought this way it could 'float a bit then simply divide by 7 days a week

Again you may want to play and change to a ROUNDUP as you will get 0 weeks if it was say created on the 2nd of the month..

Let me know if that works for you

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

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭✭✭

=ROUNDDOWN(([Created]@row - DATE(YEAR([Created]@row), MONTH([Created]@row), 1)) / 7, 0)

From the outside in

ROUND has to round it up or down I assume.. you can change the 0 to 1 to get 2.5 weeks versus say 2

I took the Date in the created row minus the 1st of the month (using the created row). You could hardcode the year or the month but I thought this way it could 'float a bit then simply divide by 7 days a week

Again you may want to play and change to a ROUNDUP as you will get 0 weeks if it was say created on the 2nd of the month..

Let me know if that works for you

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

Facilityy Professional Services Inc.

http://www.facilityy.com

• ✭✭✭
edited 02/09/23

Thank you. That seemed to work. I think the Round option is going to work better. Thanks so much!

• ✭✭✭

@Brent Wilson I did change it to round up and I am still getting 0 for some of the weeks where it starts on the 1st. Is there anything I can do to force it to give me 1?

• ✭✭✭✭✭

You could wrap it in an IF and force it to 1 if it is zero

= IF(ROUND(([Created]@row - DATE(YEAR([Created]@row), MONTH([Created]@row), 1)) / 7, 0)=0, 1, ROUND(([Created]@row - DATE(YEAR([Created]@row), MONTH([Created]@row), 1)) / 7, 0))

Obviously ROUND, ROUNDUP, ROUNDDOWN is on your preference

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!