Week number based on the month of the created date?

BFuller

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!

Brent Wilson

@BFuller

=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 Wilson

@BFuller

=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

BFuller

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

BFuller

@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?

Brent Wilson

@BFuller

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