# Date Question?

#### pinholsm

##### Board Regular
I have a Spreadheet that has in one column the start date and in another column the end date then another coulmn with the frequency of occurrences

For Example: 9-Jul-07 start 20-aug-07 end 234 frequency

the frequency code is Monday is 1 Tuesday is 2 etc Sunday is 7

I need to know how many Tuesday Wednesdys and Thursday's are in that dat range

Any Ideas?

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### morleyuk

##### Board Regular
The following I got from "The Excel Logic Page
http://geocities.com/aaronblood" but should do the job.

Create the following user function in VB Editor:

Function Days(Date1 As Long, Date2 As Long, Weekday As Integer) As Long

Application.Volatile

If Date1 < Date2 Then
x = Date1
y = Date2
Else
x = Date2
y = Date1
End If

n = 0
If Weekday = 7 Then Weekday = 0

For daycnt = x To y
If daycnt Mod 7 = Weekday Then
n = n + 1
End If
Next

Days = n

End Function

Then user the following formula in the appropriate number of ceels for days of the week you need to count:

=days('start date cell1,'end date cell','day of week No')

This function works on Sunday = 1 etc

#### Scott Huish

##### MrExcel MVP
Perhaps:

Put the days of the week in B1:B7

Formula in C1:
=SUMPRODUCT(--(WEEKDAY(\$A\$1:\$A\$10000,2)=ROW(A1)),--(\$A\$1:\$A\$10000<>""))
Copy down to C7
Book2
ABCD
17/9/2007Monday2
27/16/2007Tuesday0
37/15/2007Wednesday0
46/24/2007Thursday0
59/1/2007Friday0
6Saturday1
7Sunday2
Sheet1

#### barry houdini

##### MrExcel MVP
Do you just want the total number of those days? Perhaps like this [edited]
Book1
ABCDE
1startenddaysfrequency
2Mon 09-Jul-2007Mon 20-Aug-200723418
3Mon 09-Jul-2007Mon 20-Aug-200717
4Mon 09-Jul-2007Wed 11-Jul-200731
5Sun 01-Jul-2007Tue 31-Jul-2007123456731
6120
7
Sheet1

Formula in D2 copied down

=SUMPRODUCT(1-ISERR(FIND(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2),C2)))

#### pinholsm

##### Board Regular
WORKS AWESOME THANKS

Replies
1
Views
723
Replies
3
Views
556
Replies
1
Views
2K
Replies
0
Views
230
Replies
3
Views
473

1,191,630
Messages
5,987,774
Members
440,109
Latest member
mitra2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back