Reversing WEEKNUMBER Function

Muhammad

Hi all,

I have a few values corresponding to different dates, and I would like to group these dates by their respective weeks. What I am thinking of doing is using the WEEKNUMBER function to generate what week they are in, and then grouping them using this number. Additionally however, I would like to group them by the weeks they are in. Moreover I would like for those weeks to be represented by the Friday, and NOT by the Monday or Sunday. Is there any function that brings me close to this point, or any function that does what I am looking to do? Any help would go a long way and be greatly appreciated. Thank you all in advance!

Genevieve P.

Hi @Muhammad

You can use an IF statement to check what day of the week your date is. Then if it's a Friday, Saturday, or Sunday, you can add 1 to the WeekNumber so that the week starts on the Friday.

For example:

=IF(OR(WEEKDAY(Date@row) = 1, WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 6), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))

Then you can use this column to Group in a Report. Is this what you were looking to do?

Cheers,

Genevieve

Genevieve P.

Hi @Muhammad

You can use an IF statement to check what day of the week your date is. Then if it's a Friday, Saturday, or Sunday, you can add 1 to the WeekNumber so that the week starts on the Friday.

For example:

=IF(OR(WEEKDAY(Date@row) = 1, WEEKDAY(Date@row) = 7, WEEKDAY(Date@row) = 6), WEEKNUMBER(Date@row) + 1, WEEKNUMBER(Date@row))

Then you can use this column to Group in a Report. Is this what you were looking to do?

Cheers,

Genevieve