Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a question. I am using the following formula to determine the week number of a date, except I need to modify it so that Friday is the first day of the week. Can I modify this formula, or do I need to try a new one?

    =INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,3))+5)/7)

    Thanks

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [s]Your formula is calculating based on the first Monday of the year or Jan 3rd, I changed the formula to start on the first Friday of the year or Jan 1st.
    Try this (the 1s used to be 3s.
    :
    =INT((B4-DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,1)+WEEKDAY(DATE(YEAR(B4-WEEKDAY(B4-1)+4),1,1))+5)/7)[/s]

    I apologize as I did not test fully.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This one should do it.

    =INT((B4-DATEVALUE("1/1/2010"))/7)+1

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This formula will calculate based on current year - you should not have to change it from year to year.

    change the -6 to the weekday number that you want to start your week - with Sunday = 1, Monday = 2, etc

    =INT(($B2-(DATE(YEAR($B2),1,1) - WEEKDAY(DATE(YEAR($B2),1,1)-6)+7))/7)+1

  5. #5
    New Lounger
    Join Date
    Jan 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Works perfect! Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •