Counting specific days between dates
Hello
I'm trying to calculate, say, the number of Tuesdays between 17/06/14 and 16/03/15. It would also be helpful if I could work out how many there are in a given month, including the dates given (especially where the start/end date falls on the specific day being counted).
Many thanks!
floxxie
Here is a file that does just that.
Days of specific type between two dates.xlsm
Bernie Deitrick
Excel MVP 2000-2010
This will count the number of Tuesdays between the start date and end date (inclusive):
Data Range
=INT((WEEKDAY(A2-2,2)+B2-A2)/7)
That formula will work in any version of Excel. If you're using Excel 2010 or later then we can use this formula (fewer moving parts!):
=NETWORKDAYS.INTL(A2,B2,"1011111")
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Or this
=ROUND(DATEDIF(A1,B1,"d")/7,0)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
With some light testing that doesn't seem to be reliable.Originally Posted by AlKey
8/9/2011 - 1/22/2014
11/20/2006 - 10/2/2014
9/26/2000 - 1/8/2013
Hello Bernie
Many thanks for this. I guess there is a reason why the formula is complicated becuase it appears to be the only one that has worked for me.
Kind regards
floxxie
Hi Tony
I tried the formula but it did not seem to work for me. I am wondering if it is anything to do with the way we enter dates (UK dd/mm/yy vs US mm/dd/yy).....?
And when you say weekday, I guess this is where I enter the day of the week I want counted? No quotation marks?
Tony Valko's formula works for me.... though to make it easier to change from Tuesdays to any day:
=INT((WEEKDAY(A2-(MATCH("Wednesday",{"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"},FALSE)-1),2)+B2-A2)/7)
Last edited by Bernie Deitrick; 05-13-2014 at 01:04 PM.
The date format doesn't matter. That's for display purposes only.
The generalized formula is:
=INT((WEEKDAY(Start_Date-n,2)+End_Date-Start_Date)/7)
Where n = a weekday number from 1 to 7:
1 = Monday
2 = Tuesday
3 = Wednesday
4 = Thursday
5 = Friday
6 = Saturday
7 = Sunday
Last edited by Tony Valko; 05-13-2014 at 04:11 PM.
Tony and Bernie,
Thank you very much, much clearer now!
Kind regards
floxxie.
You're welcome. We appreciate the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks