none
Sharepoint converting a date to a week number RRS feed

  • Question

  • I have a date column that is formatted to display 2/14 for February 14.

    I would like to also show this date as the proper week number for the current year.  I know I need to add a column, but am looking for help in writing the formula to find the proper week number for 2013; like week 6.

    thank you in advance for helping.
    Dave

    Tuesday, February 19, 2013 11:29 PM

Answers

  • Hi davematz,

    I tested Bjoern's script and I think it works for your requirement.

    To get week number of a2, create a calculated column and add following script as its formula. Then you can get correct week number in this calculated column.

    =INT(([a2]-DATE(YEAR([a2]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([a2]),1,1)),"d")))/7)

    And to get wekk number of h2, create another calculated column and add following script.

    =INT(([h2]-DATE(YEAR([h2]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([h2]),1,1)),"d")))/7)

    Thanks & Regards,
    Emir


    Emir Liu
    TechNet Community Support

    • Marked as answer by davematz Tuesday, February 26, 2013 10:09 PM
    Thursday, February 21, 2013 9:44 AM

All replies

  • Try this one:

    =INT(([Today]-DATE(YEAR([Today]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([Today]),1,1)),"d")))/7)
    


    Kind Regards

    Bjoern
    http://www.sharepointviking.com
    Twitter: Follow @bjoern_rapp

    Wednesday, February 20, 2013 12:01 AM
    Moderator
  • this didn't work....

    cell a2 is formatted for m/d and value of a2 = 2/21

    cell h2 is formatted m/d/y and value of h2 = 2/15/13

    I need to show in a new column the week number for year 2013 for the value of a2 & h2

    I only want the week number...so if 2/21 is week 8 I only want to see an 8 and not 1/8.

    For the other week number if 2/15/13 is week 7 i only want to see the 7, not any other numbers.

    • Edited by davematz Wednesday, February 20, 2013 12:33 AM
    • Marked as answer by davematz Tuesday, February 26, 2013 10:09 PM
    • Unmarked as answer by davematz Tuesday, February 26, 2013 10:09 PM
    Wednesday, February 20, 2013 12:27 AM
  • Hi davematz,

    I tested Bjoern's script and I think it works for your requirement.

    To get week number of a2, create a calculated column and add following script as its formula. Then you can get correct week number in this calculated column.

    =INT(([a2]-DATE(YEAR([a2]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([a2]),1,1)),"d")))/7)

    And to get wekk number of h2, create another calculated column and add following script.

    =INT(([h2]-DATE(YEAR([h2]),1,1)+(TEXT(WEEKDAY(DATE(YEAR([h2]),1,1)),"d")))/7)

    Thanks & Regards,
    Emir


    Emir Liu
    TechNet Community Support

    • Marked as answer by davematz Tuesday, February 26, 2013 10:09 PM
    Thursday, February 21, 2013 9:44 AM