Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Week Day Week of Month

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My company schedules services to customers based upon a fixed week and
    weekday of each month, i.e.:

    "1st Monday" of each month or "3rd Friday" of each month, and so on.

    Every schedule excludes Sundays, but otherwise can be anywhere from 1-1
    (1st Monday) through 4-6 (fourth Saturday).

    If all I have is the last calendar DATE of the last service, how can I
    write an excel formula to extract which week of month and day of the
    week the service was actually performed? Monday must equal "1" and
    Saturday must equal "6'. For all intent and purposes, we 'ignore' 5th
    week-days of the month (they are not used for fixed appointments).

    I've looked and tried to use all examples posted at other sites and your
    website but
    cannot find a series that works. I am not opposed to breaking the
    action into 'steps' - just need a solution that works.


  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-04 03:27, Gary Kaye wrote:
    My company schedules services to customers based upon a fixed week and
    weekday of each month, i.e.:

    "1st Monday" of each month or "3rd Friday" of each month, and so on.

    Every schedule excludes Sundays, but otherwise can be anywhere from 1-1
    (1st Monday) through 4-6 (fourth Saturday).

    If all I have is the last calendar DATE of the last service, how can I
    write an excel formula to extract which week of month and day of the
    week the service was actually performed? Monday must equal "1" and
    Saturday must equal "6'. For all intent and purposes, we 'ignore' 5th
    week-days of the month (they are not used for fixed appointments).

    I've looked and tried to use all examples posted at other sites and your
    website but
    cannot find a series that works. I am not opposed to breaking the
    action into 'steps' - just need a solution that works.
    Not sure I understand what you're asking, but

    =WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))

    would give you 'week of month'.

    =WEEKDAY(A1)

    would give you the day of the week as number and

    =CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

    would give you the day of the week as name [ Thanks to Ivan ].



    [ This Message was edited by: aladin akyurek on 2002-05-04 04:41 ]

    [ This Message was edited by: aladin akyurek on 2002-05-04 05:15 ]

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-04 04:28, Aladin Akyurek wrote:

    Not sure I understand what you're asking, but

    =WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))

    would give you 'week of month'.

    =WEEKDAY(A1)

    would give you the day of the week as number and

    =CHOOSE(WEEKDAY(A1),"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")

    would give you the day of the week as name.



    [ This Message was edited by: aladin akyurek on 2002-05-04 04:41 ]
    Also Note: You need the Analysis ToolPac
    Addin for the WeekNum function.

    Plus =WEEKDAY(A1,2) would give you 6 for Sat

    I think WEEKDAY(A1) defaults to 1 = Sunday to 7 = Saturday, giving you 7 for Sat and you
    say you need 6


    Kind Regards,
    Ivan F Moala From the City of Sails

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default


    Also Note: You need the Analysis ToolPac
    Addin for the WeekNum function.

    Plus =WEEKDAY(A1,2) would give you 6 for Sat

    I think WEEKDAY(A1) defaults to 1 = Sunday to 7 = Saturday, giving you 7 for Sat and you
    say you need 6


    Yes. Thanks for pointing out that. I edited the CHOOSE list, rather than adding a specifier to WEEKNUM itself.

    What about the longer formula? That's the one I'm most worried about.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-04 05:20 ]

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-04 05:19, Aladin Akyurek wrote:

    Also Note: You need the Analysis ToolPac
    Addin for the WeekNum function.

    Plus =WEEKDAY(A1,2) would give you 6 for Sat

    I think WEEKDAY(A1) defaults to 1 = Sunday to 7 = Saturday, giving you 7 for Sat and you
    say you need 6


    Yes. Thanks for pointing out that. I edited the CHOOSE list, rather than adding a specifier to WEEKNUM itself.

    What about the longer formula? That's the one I'm most worried about.

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-04 05:20 ]
    Yes your right, I guess you can always just
    ref the cell =A1 and just format it as "ddd"
    date format......
    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Regarding the problem "Week of Month and Week of Day" posted earlier...

    Does not work as I need.

    Formula suggested fails on this basis alone:

    If the 4th or 5th week of any month ends, lets say on a "Wednesday" the formula may show it expressed correctly as "5-3" for 5th Wednesday. However the next day, Thursday is in the 1st week of June, but will be expressed as "5-4" rather than "1-4" for 1st Thursday as I would expect the 1st Thursday of the month to read...

    Whatever the solution, each month can only have a maximum of 5 weeks and any one day of the week must be associated with the proper week of the month. Sounds simple, but...

    Hope this problem doesn't fry anyone's brain as it has mine...

    Gary.

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-05 08:15, Gary Kaye wrote:
    Regarding the problem "Week of Month and Week of Day" posted earlier...

    Does not work as I need.

    Formula suggested fails on this basis alone:

    If the 4th or 5th week of any month ends, lets say on a "Wednesday" the formula may show it expressed correctly as "5-3" for 5th Wednesday. However the next day, Thursday is in the 1st week of June, but will be expressed as "5-4" rather than "1-4" for 1st Thursday as I would expect the 1st Thursday of the month to read...

    Whatever the solution, each month can only have a maximum of 5 weeks and any one day of the week must be associated with the proper week of the month. Sounds simple, but...

    Hope this problem doesn't fry anyone's brain as it has mine...

    Gary.
    American and European week numbering differ, a fact that makes difficult for me to check the correctness by comparing what my American Excel grinds out with what my European "office agenda" says. So I'd like to know which formula meets your need if any:


    1) =IF(MONTH(A1)=1,WEEKNUM(A1),(WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)))+1)


    2) =IF(MONTH(A1)=1,WEEKNUM(A1),IF(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0)),1,WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+1))


    3) =IF(MONTH(A1)=1,WEEKNUM(A1),(WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))))+(ROUND((EOMONTH(A1,0)-A1+1)/7,0)=5)


    4) =IF(MONTH(A1)=1,WEEKNUM(A1),WEEKNUM(A1)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))+(WEEKNUM(A1)=WEEKNUM(DATE(YEAR(A1),MONTH(A1),0))))


    Maybe none of the above.


    Regards,


    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-05-05 09:01 ]

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Aladin...but

    Your 3rd and 4th suggested formulas would not even get past the 'Value' error and I could not fix the cause. 1 and 2 did but still returned errors in identifying which week of what month I expect. Looks like I'll have to manually identify over 5,000 records and dates to determine whether they express a 1st, 2nd, 3rd or 4th week of any given month correctly. The Day of Week has not been an issue at all. Thanks anyway. I'll watch for any further suggestions.

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

    Default

    This may/may not be what you're looking for??
    A majority of the function was taken from John Walkenbach (although I added to the function to meet your needs).
    In a1 put the day number (1=Mon...7=Sun)
    In b1 put the occurrence (if you wanted the 2nd week of the month put 2 in the cell)
    In a2 put a date
    In b2 put =DATE(YEAR(A2),MONTH(A2),1)+IF($A$1 this will extract the target date and finally put in c2 =WEEKNUM(B2,2)+1-WEEKNUM(DATE(YEAR(B2),MONTH(B2),1),2)&"-"&$A$1 this will give you the day# and week# combo.
    HTH,
    Drew


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

    [ This Message was edited by: Drew on 2002-05-07 13:25 ]

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-07 02:16, Gary Kaye wrote:
    Thanks Aladin...but

    Your 3rd and 4th suggested formulas would not even get past the 'Value' error and I could not fix the cause. 1 and 2 did but still returned errors in identifying which week of what month I expect. Looks like I'll have to manually identify over 5,000 records and dates to determine whether they express a 1st, 2nd, 3rd or 4th week of any given month correctly. The Day of Week has not been an issue at all. Thanks anyway. I'll watch for any further suggestions.
    Gary,

    Your mail address in your profile is dydsfunctional. If you like, try mine:

    aladin_akyurek@yahoo.com

    Aladin

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
  •