+ Reply to Thread
Results 1 to 4 of 4

Calculating How Many Mondays to Fridays are in a Specific Month, Between Two Dates

  1. 05-14-2015, 10:06 AM #1
    brenden101
    brenden101 is offline
    Registered User
    Join Date
    02-23-2015
    Location
    South Africa
    MS-Off Ver
    2013
    Posts
    5

    Calculating How Many Mondays to Fridays are in a Specific Month, Between Two Dates

    Hi Excel Forum,

    First off, thank you for saving my behind more times than I can count.

    I'm stuck with a complicated (to me atleast!) formula, which I need some help with.

    I'll try make this as transparent as possible. I need to calculate how many Mondays, Tues, Wed, Thur, and Fridays there are between two specific dates in a range. Then take it further by grouping them into Months. The months span over multiple years.

    I've attached a spreadsheet with a similarly looking layout.

    Thanks in advance for any feedback.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-14-2015, 11:25 AM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculating How Many Mondays to Fridays are in a Specific Month, Between Two Dates

    Try this formula in H4 copied across and down

    =SUMPRODUCT(COUNTIFS($C:$C,"<="&(8&$G4&$F4)-WEEKDAY(7-COLUMNS($H4:H4)&$G4&$F4)+{0,7,14,21,28},$D:$D,">="&(8&$G4&$F4)-WEEKDAY(7-COLUMNS($H4:H4)&$G4&$F4)+{0,7,14,21,28}),(TEXT((8&$G4&$F4)-WEEKDAY(7-COLUMNS($H4:H4)&$G4&$F4)+{0,7,14,21,28},"mmmm")=$G4)+0)
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 05-14-2015, 11:26 AM #3
    jhren
    jhren is offline
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Calculating How Many Mondays to Fridays are in a Specific Month, Between Two Dates

    How do the employee start and end dates figure into it?
    Reply With QuoteRegister To Reply
  4. 05-14-2015, 11:28 AM #4
    k64
    k64 is offline
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Calculating How Many Mondays to Fridays are in a Specific Month, Between Two Dates

    I'm impressed daddylonglegs!

    Here's what I have, it's a macro. Just open the VBA editor, insert a new module, paste this in, and run it. 

    Please Login or Register  to view this content.
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Return the dates of all the mondays and fridays in a month
    By all4excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-28-2007, 07:20 AM
  2. how many Fridays in a specific month in Excel
    By Don Ray in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-08-2006, 11:45 PM
  3. Calculating the number of Fridays in a month
    By Greg Ward in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2005, 02:06 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1