locked
Week number by Month with week starting saturday RRS feed

  • Question

  • I am looking forward to get week numbers according to months

    for example for the month of jan i should get 1 week, 2 week

    then for feb 1 week , 2 week etc.

    i have below code to get the week number starting week Sunday

    =((INT((TransDate-DATE(YEAR(TransDate),1,1)+(TEXT(WEEKDAY(DATE(YEAR(TransDate),1,1)),"d")))/7)+1)-
    (INT(((MONTH(TransDate)&"/1/"&YEAR(TransDate))-DATE(YEAR((MONTH(TransDate)&"/1/"&YEAR(TransDate))),1,1)+
    (TEXT(WEEKDAY(DATE(YEAR((MONTH(TransDate)&"/1/"&YEAR(TransDate))),1,1)),"d")))/7)+1)+1)&" "&"Week"

    Can any one help me to get the starting of week as saturday instead of Sunday from the same code.

    Thank you

    Tuesday, May 1, 2018 8:04 AM

All replies

  • Hi,

    Try below formula:

    =((INT((Date-DATE(YEAR(Date),1,7)+(TEXT(WEEKDAY(DATE(YEAR(Date),1,7)),"d")))/7)+1)-(INT(((MONTH(Date)&"/1/"&YEAR(Date))-DATE(YEAR((MONTH(Date)&"/1/"&YEAR(Date))),1,7)+(TEXT(WEEKDAY(DATE(YEAR((MONTH(Date)&"/1/"&YEAR(Date))),1,7)),"d")))/7)+1)+1)&" "&"Week"

    Thanks,

    Siva

    Mark it as an answer if it resolve your problem.
    • Edited by Siva Padala Wednesday, May 2, 2018 11:13 AM
    Wednesday, May 2, 2018 11:05 AM
  • Hi Siva,

    Thank you so much for the response.

    I have tired this code.

    I got the week starting as Thursday with this code.

    However i need to have week starting with Saturday as the first day.

    Kindly help me on this.

    Thank you.

    Sunday, May 6, 2018 4:40 AM
  • Try to change the 1 value all places in the formula form 1 - 7 and check. 

    Thanks,

    Siva

    Tuesday, May 15, 2018 11:18 AM