+ Reply to Thread
Results 1 to 6 of 6

How many week days in a month

  1. #1
    Registered User
    Join Date
    01-19-2007
    Posts
    9

    How many week days in a month

    Hello again, more help for the same workbook if possible please.

    I need to be able to quickly check to see how many mondays, tuesdays, wednesdays, thursdays and fridays there are in any given month.

    so for example
    a2 = January 2007 (01/07)
    a3 = february 2007 (02/07)
    etc

    then
    b1 = monday
    c1 = tuesday
    etc

    then
    b2 = number of mondays in January
    c2 = number of tuesdays in January
    b3 = number of mondays in February
    c3 = number of tuesdays in February

    Ive posted an example to explain in google to explain what I mean here
    any help greatly appreciated
    Laurie

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Assuming A2 downwards is always the 1st day of the month, formatted how you want and b1, c1 are days of the week in text format (without an "s" on the end) then you could use either of these formulas in B2 copied across and down

    =INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+$A3-$A2-1)/7)

    or

    =SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2&":"&$A2+30)),"ddddm")=B$1& MONTH($A2)))

    the first is more efficient but requires that there is always an extra date in column A, i.e. if you want to calculate for the whole of 2007, up to december then you have to also include 1st January 2008

    edit: sorry glitch in 1st formula, edited version above is correct...
    Last edited by daddylonglegs; 02-06-2007 at 12:17 PM.

  3. #3
    Registered User
    Join Date
    01-19-2007
    Posts
    9
    [QUOTE=daddylonglegs]Assuming A2 downwards is always the 1st day of the month, formatted how you want and b1, c1 are days of the week in text format (without an "s" on the end) then you could use either of these formulas in B2 copied across and down

    =INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+$A3-$A2-1)/7)

    or

    =SUMPRODUCT(--(TEXT(ROW(INDIRECT($A2&":"&$A2+30)),"ddddm")=B$1& MONTH($A2)))

    the first is more efficient but requires that there is always an extra date in column A, i.e. if you want to calculate for the whole of 2007, up to december then you have to also include 1st January 2008

    edit: sorry glitch in 1st formula, edited version above is correct...
    QUOTE]
    wow, thank you so much, that is exactly what I needed, and I never would of worked that out for myself. Thanks :-)
    I used the first formula, as I will probably still need this next year, so I included 1/1/2008 but it comes up with -5636 or -5635 (depending on the day) is this because we are in 2007 and in 2008 it would format correctly?
    Thank you SO much you've really helped me out lots.
    Laurie

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    That formula won't work for the last row, you always need the extra date, so if you have dates down to A20 for instance then the formula will only work down to row 19

    You can use a formula that will work on any row, it's just a bit longer, thats all

    Either of these in B2

    =INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+DAY(DATE(YEAR($A2),MONTH($A2)+1,-1)))/7)

    or using EOMONTH function from Analysis ToolPak

    =INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+DAY(EOMONTH($A2,0)-1))/7)

  5. #5
    Registered User
    Join Date
    01-19-2007
    Posts
    9
    =INT((WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+DAY(DATE(YEAR($A2),MONTH($ A2)+1,-1)))/7)
    wow ... fantastic, thank you. I am truly amazed, I cant even begin to understand this formula, but its going to save me a lot of brain-ache Thank you so much

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    every time I look at this I find a different way to do it. This is possibly the best.....

    =4+(DAY($A2-WEEKDAY($A2-MATCH(B$1,$A$1:$H$1,0))+35)>9)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1