Number of Specific Day within Dates

[Abdul]

Hi,

Is there a way to find how many Day (eg. wednesdays) within a a date
range?

Like how many Wednesdays (any given Day) falling between June 1, 2009
Till June 21, 2009

Thanks,

 

[Alan McQ via OfficeKB.com]

Hi Abdul,

You can use the below function to count the days. First though, you will need
to copy the code at the end of this post into a VBA module.

A1 B1 C1 D1
1 Jun 2009 30 Jun 2009 Thursday =countday(A1,B1,C1)


Public Function CountDay(StartDate As Date, EndDate As Date, DayName As
String) As Long
Dim iDate As Date, dCount As Long

iDate = StartDate
dCount = 0

Do Until iDate > EndDate
If Not iDate > EndDate Then
If Format(iDate, "dddd") = DayName Then
dCount = dCount + 1
End If
End If
iDate = iDate + 1
Loop

CountDay = dCount

End Function

 

[Teethless mama]

=End date - Start date

 

[Shane Devenshire]

Hi,

You really need to give us more info. If you are trying to count the number
of days between two dates in cell A1 and A2 then
=A2-A1
If you are trying to count the number of days between A1 and today, then
=TODAY()-A1
If....

 

[zxcv]

Hi,

Is there a way to find how many Day (eg. wednesdays) within a a date
range?

Like how many Wednesdays (any given Day) falling between June 1, 2009
Till June 21, 2009

Thanks,

Let's say that you have a sheet set up like this, A2 shows your start
date, B2 shows your end date, and C2 though I2 will have the count of
Sundays through Saturdays, try this formula in C2 through I2:

=FLOOR(($B2-$A2+1)/7,1)+IF(($B2-$A2+1)/7>FLOOR(($B2-$A2+1)/7,1),IF
(WEEKDAY($B2)>=WEEKDAY($A2),IF(AND(WEEKDAY($B2)>=COLUMN()-2,COLUMN
()-2>=WEEKDAY($A2)),1,0),IF(OR(AND(COLUMN()-2<=WEEKDAY($A2),COLUMN
()-2<=WEEKDAY($B2)),AND(COLUMN()-2>=WEEKDAY($A2),COLUMN()-2>=WEEKDAY
($B2))),1,0)),0)

 

[Ron Rosenfeld]

Let's say that you have a sheet set up like this, A2 shows your start
date, B2 shows your end date, and C2 though I2 will have the count of
Sundays through Saturdays, try this formula in C2 through I2:

=FLOOR(($B2-$A2+1)/7,1)+IF(($B2-$A2+1)/7>FLOOR(($B2-$A2+1)/7,1),IF
(WEEKDAY($B2)>=WEEKDAY($A2),IF(AND(WEEKDAY($B2)>=COLUMN()-2,COLUMN
()-2>=WEEKDAY($A2)),1,0),IF(OR(AND(COLUMN()-2<=WEEKDAY($A2),COLUMN
()-2<=WEEKDAY($B2)),AND(COLUMN()-2>=WEEKDAY($A2),COLUMN()-2>=WEEKDAY
($B2))),1,0)),0)

Tagging on here because I don't see the original post. But a simpler formula:

The number of DOW (1= Sunday, 2 = Monday, ..., 7 = Saturday) between a start
date (A1) and an end date (A2) is :

=INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7)
--ron

 

[zxcv]

Tagging on here because I don't see the original post.  But a simpler formula:

The number of DOW (1= Sunday, 2 = Monday, ..., 7 = Saturday) between a start
date (A1) and an end date (A2) is :

=INT((A2-WEEKDAY(A2+1-DOW)-A1+8)/7)
--ron- Hide quoted text -

- Show quoted text -

Thanks. That was much better.

 

[jamescox]

Not absolutely certain this is correct, so check it out for yourself:

Cell B2 => 23-Jun-09
Cell B3 => 16-Jul-09

If you want to count both the first and last Wednesday, if both dates
are Wednesdays, use

=INT((B3-B2)/7)+IF(OR(WEEKDAY(B2,1)<=4,WEEKDAY(B3,1)=4),1,0)

If both the start and end dates are Wednedays and you only want to
count one of them, use

=INT((B3-B2)/7)+IF(WEEKDAY(B2,1)<=4,1,0)+IF(WEEKDAY(B3,1)=4,-1,0)

You may get a simpler answer from someone...

Hope this helps!