1

I'm looking for a calculated column formula which will calculate 1st January 2022 as week 1. I'm in Europe, so, like most of the world, my date field 01/01/2022 = 1st January 2022.

I would like 1st January til 7th January to be week 1. 8th January - 14th January should be week 2.

How the desired results would look

I've found loads of articles for calculating other types of dates like the ISO date or Financial Year dates, but mine should be simple and yet I can't seem to find a formula which can do this. The closest I found was by Nate Chamberlain, but it didn't return the weeks correctly for me.

Update - extra notes relating to Santosh's answer

For other years, past and future, a separate calculated column is needed. Please see the screen shot below to illustrate why this is needed.

The formula provided by Santosh needs to be adjusted very slightly to allow it to function for each year; very simple.

Picture to show how past and future years are handled

Improve this question

3 Answers 3

Reset to default
1

I have created below formula to calculate week number starting on 1st of Jan 2022. Refer attached screenshot for the output.

=ROUNDDOWN(DATEDIF(DATE(2022,1,1),[datefield],"d")/7,0)+1

You need to replace datefield with your date column, the data type returned by this formula can be used as Number or single line of text. This should work for any regional settings, please confirm.

This formula can be used to calculate week number starting with any starting date(e.g. In this case we have used 1st Jan, 2022).

Output :

Output of my calculated column formula :

I hope this will work for you :)

Improve this answer
2
  • Thanks Santosh, that works really well! I've updated my question with a note for other users regarding how to handle past or future years.
    – Tally
    Commented Jan 11, 2022 at 13:31
  • Thanks for the reply. I'm glad to hear that solution worked for you.
    – Santosh Sarnobat
    Commented Jan 11, 2022 at 16:34
2

I found that Santosh's answer worked, but my case required that the week number is relative to the year of the respective [DATEFIELD], not based on a static starting date. I've adapted his answer and found that the following code provided what I need.

=ROUNDDOWN(DATEDIF(DATE(YEAR([DATEFIELD]),1,1),[DATEFIELD],"d")/7,0)+1
Improve this answer
1
  • Good work Brad!
    – Tally
    Commented Jan 13, 2023 at 9:57
0

So glad I found this. I found Brad's answer met my needs more precisely. Then I added code to show a blank field if there is no date in the date field. My code looks like this and will display an empty field:

IF(ISBLANK([DATEFIELD]),"",ROUNDDOWN(DATEDIF(DATE(YEAR([DATEFIELD]),1,1),[DATEFIELD],"d")/7,0)+1
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.