Compartir a través de

Calculate calendar week from a date field

  • Artículo

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

  • Anonymous
    January 01, 2003
    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!

  • Anonymous
    February 07, 2012
    The formula contains a syntax error or is not supported.

  • Anonymous
    January 23, 2013
    How do I change this formula so it reflects the week starting on Sunday? Thank you!

  • Anonymous
    March 08, 2014
    Change the "-9" to a "-12" will correct for a week that starts on a Saturday.

  • Anonymous
    April 28, 2014
    start week monday

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

  • Anonymous
    July 04, 2014
    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.

  • Anonymous
    August 11, 2014
    How do I change this formula so it reflects the week starting on Friday morning please?