+ Reply to Thread
Results 1 to 4 of 4

formula to calculate numbers of days (i.e. Monday's) in a month

  1. #1
    Registered User
    Join Date
    07-04-2007
    Posts
    2

    formula to calculate numbers of days (i.e. Monday's) in a month

    I have been "volunteered" to set up a spreadsheet to speed up the tuition statements at the daycare center I work in. I have a nodding acquaintance with Excel but have no idea how do determine the number of Monday's or any other day of the week there are in a given month.

    Our children attend the center from two days to five days a week, some half day and some full day. Each child attends the same days of the week. What is needed is a way to calculate the number individual days per month (i.e.: Mondays) in a given month.

    The rest of the spreadsheet will be a piece of cake (I hope), I simply would multiply the number of Monday's times the appropriate fee, then the number of Tuesday's times the appropriate fee and so on. The total would then be billed.

    Thank you in advance for all your help.

    Carol

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    Here's one way...

    With
    A1: (any date)
    B1: (a day reference)…..where: 1:Sun, 2:Mon…..7:Sat

    This formula counts the number of a specific weekday within that range
    =SUM(INT((WEEKDAY(A1-DAY(A1)+1-B1)+(DAY(DATE(YEAR(A1),MONTH(A1)+1,0))-1))/7))

    OR….if the Analysis ToolPak is installed
    =SUMPRODUCT(--(WEEKDAY(A1-1+ROW($A$1:INDEX(A:A,DAY(EOMONTH(A1,0)))))=B1))

    If A1: 06/15/2007 and B1: 2
    Then the formula returns 4 (there are 4 Mondays in June 2007)

    Does that help?

    Regards,

    Ron
    Microsoft MVP (Excel)

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    This way's shorter....

    A1: (a date)
    B1: (a day reference)…..where: 1:Sun, 2:Mon…..7:Sat

    This formula returns the number of times that day occurs in the month containing the date in A1:

    =4+(DAY(A1+MATCH($A$2,WEEKDAY(A1+{0,1,2,3,4,5,6},2),0)+27)>8)

    (I think there's an even shorter way, but I'll be darned if I can think of it right now!)

    Regards,

    Ron
    Microsoft MVP (Excel)

  4. #4
    Registered User
    Join Date
    07-04-2007
    Posts
    2

    Thank You - I will try it tomorrow (Saturday)

    Thank you Ron

    I will try both formulas tomorrow and let you know how I make out.

    Carol

+ 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