Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Week Day Week of Month

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Location
    Boston, Mass
    Posts
    182
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    b2's post seems to be getting cut off.

    =DATE(YEAR(A2),MONTH(A2),1)+IF($A$1


    [ This Message was edited by: Drew on 2002-05-07 10:57 ]

    [ This Message was edited by: Drew on 2002-05-07 11:55 ]

  2. #12
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    one more for fun

    =INT(DAY(A1)/7)+1&CHOOSE(INT(DAY(A1)/7)+1,"st","nd","rd","th","th")&TEXT(A1," dddd")

    good luck

    EDIT - I suppose fun should somehow equate to working, how about a revised formula of
    =INT(DAY(A1)/7.5)+1&CHOOSE(INT(DAY(A1)/7.5)+1,"st","nd","rd","th","th")&TEXT(A1," dddd")


    FINAL EDIT ? to you format
    =INT(DAY(A1)/7.5)+1&"-"&WEEKDAY(A1,2)
    [ This Message was edited by: IML on 2002-05-07 10:49 ]

    [ This Message was edited by: iml on 2002-05-07 11:00 ]

  3. #13
    New Member
    Join Date
    Aug 2004
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Week Day Week of Month

    Not 100% sure of the needs here, but the first formula doesn't quite work. I have a shorter, and easier to understand method for finding "Week of the Month"

    The problem comes in when a month STARTS mid-week. Lets say the month STARTS on Friday, so Monday is the 4th. Monday should be in the 2nd week.

    The solution is you need to know the week of the TARGET date AND the week of the FIRST of the month. Using these two weeks, we can "normalize" to something like the following:

    =WEEKNUM(B2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1))+1

    B2 is the cell with the date in question.

    The first weeknum gets the week (of the year) of the target date. The second gets the week (of the year) of the 1st of the current month. You subtract and then add 1, so the first week will be 1 not zero.

    This is how you can get the week of the month from a given date.

    Now I was confused on if you were trying to convert BACKWARDS (as above) or have it calculate the "next" date.

    If you are trying to find the "next" date then you must add either 28 or 35 (4 weeks or 5) to your current date, and make sure it is in fact the next month:

    =IF(MONTH(B2)=MONTH(B2+28),B2+35,B2+28)

    This checks to see if the target date+28 is the same month, if so, then add 35, otherwise, add 28.

    Hope this helps.

  4. #14
    New Member
    Join Date
    Aug 2004
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Week Day Week of Month

    Not 100% sure of the needs here, but the first formula doesn't quite work. I have a shorter, and easier to understand method for finding "Week of the Month"

    The problem comes in when a month STARTS mid-week. Lets say the month STARTS on Friday, so Monday is the 4th. Monday should be in the 2nd week.

    The solution is you need to know the week of the TARGET date AND the week of the FIRST of the month. Using these two weeks, we can "normalize" to something like the following:

    =WEEKNUM(B2)-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1))+1

    B2 is the cell with the date in question.

    The first weeknum gets the week (of the year) of the target date. The second gets the week (of the year) of the 1st of the current month. You subtract and then add 1, so the first week will be 1 not zero.

    This is how you can get the week of the month from a given date.

    Now I was confused on if you were trying to convert BACKWARDS (as above) or have it calculate the "next" date.

    If you are trying to find the "next" date then you must add either 28 or 35 (4 weeks or 5) to your current date, and make sure it is in fact the next month:

    =IF(MONTH(B2)=MONTH(B2+28),B2+35,B2+28)

    This checks to see if the target date+28 is the same month, if so, then add 35, otherwise, add 28.

    Hope this helps.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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