Calculate calendar week from a date field


If you want to calculate the calendar week number from a given date field you should create a new column of type "Calculated (calculation based on other columns)" and apply the following formula to the field, assuming that your date field column is called "Datum":

=IF(Datum<>"";(ROUND((Datum-DATE(YEAR(Datum+3-MOD(Datum-2;7));1;MOD(-2;7)-9))/7;0));"")


Comments (7)

  1. Anonymous says:

    if you're in the US, you need to replace the semi-colons ";" used in the above formula with commas "," as the delimiter character. So the formula in this case will be:

    =IF(Datum<>"",(ROUND((Datum-DATE(YEAR(Datum+3-MOD(Datum-2,7)),1,MOD(-2,7)-9))/7,0)),"")

    Good luck!

  2. Does not work says:

    The formula contains a syntax error or is not supported.

  3. Emilie Nichols says:

    How do I change this formula so it reflects the week starting on Sunday?

    Thank you!

  4. Douglas Fagerstrom says:

    Change the "-9" to a "-12" will correct for a week that starts on a Saturday.

  5. sight says:

    start week monday

    =IF(Datum<>"";(ROUND((Datum-DATE(YEAR(Datum+1-MOD([Data aktualna]+4;7));1;MOD(4;7)-9))/7;0));"")

  6. Sercan ACAN says:

    hi,

    I guess this formule acts as Tuesday is the beginning of week. when I choose a monday date it gaves me the numvber of 1 week older.

  7. Yan Richards says:

    How do I change this formula so it reflects the week starting on Friday morning please?

Skip to main content