Count days of the week

L

Loucurr

New Member
Joined
Apr 21, 2008
Messages
11
I have a sheet with a column containing the date format of "m/d/yy dddd". I want to be able to count the number of occurances of each day of the week.

I tried to format the next column as =text([cell], dddd). It works but the problem with this is that when it is copied into a row that does not currently have a corresponding date the entered the reference reads [saturday] and this will throw off my stats.

Any ideas???:confused:
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
If you have dates in A1:A100 this will count the number of Saturdays and ignore blanks

=SUMPRODUCT((WEEKDAY(A1:A100)=7)*(A1:A100<>""))

For other days use 1 to 6 (Sunday to Friday)
 
Upvote 0
If all cells in the range contain dates or blanks then that formula should work.....but if you have text in that range you might get a #VALUE! error. Can you post the exact formula you tried?

This variation would ignore text (unless that text is a day of the week!)

=SUMPRODUCT((TEXT(A1:A100,"ddd")="sat")*(A1:A100<>""))

Or......you could try your original approach but filter out blanks, e.g. use a helper column with a formula like

=IF(A1="","",TEXT(A1,"ddd"))

then use COUNTIF on the helper column.......
 
Upvote 0
This is the formula starting with E2 because E1 is the column heading.

=SUMPRODUCT((WEEKDAY(E2:E100)=7)*(E2:E100<>""))
 
Upvote 0
OK, formula looks great. If it gives a #VALUE! error I believe that can only happen if you have either #VALUE! errors in E2:E100 or, as previously advised, some of E2:E100 are not dates or blanks (in which case WEEKDAY gives an error).

I suggest you check that all entries are dates. COUNT(E2:E100) should correspond to the number of dates in the range. Otherwise try one of the other options I suggested.....
 
Upvote 0
Ok, I tried this and it works, blank cells are blank when there is no date.

=IF(A1="","",TEXT(A1,"ddd"))

then use COUNTIF on the helper column.......

Now, the COUNTIF function is not counting the occurances.

Some times youy just want to beat your computer! :mad:

Thanks for the help thus far.
 
Upvote 0
Barry,

Thanks alot! sometimes it is the small things you overlook. I forgot to reference the worksheet when I set up the formula.

Works great!
 
Upvote 0
Hi LouCurr:

Following is a solution using EXCEL's one-variable DataTable feature ...

<html><head><title>Excel Jeanie HTML</title></head><body>
Excel Workbook
ABCDEFG
1Date1*****datBase
212/01/08 Saturday*FALSE***criteria
35/02/08 Tuesday*****DataTable columnInputCell
429/02/08 Friday*DataTable0**DataTable Formula
524/03/08 Monday*Sunday0***
617/04/08 Thursday*Monday2***
7**Tuesday3***
84/06/08 Wednesday*Wednesday1***
928/06/05 Tuesday*Thursday1***
1022/07/08 Tuesday*Friday2***
1115/08/08 Friday*Saturday1***
128/09/08 Monday******
13*******
Sheet3


</body></html>
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
VBA or Excel – Time sheet with RT / OT based on the day of the week.
Replies
5
Views
241
awoohaw
awoohaw
A
  • Question
Creating a Calendar for a Month with tasks
Replies
0
Views
104
anuradhagrewal
A
C
  • Question
Ordinary Hours or Penalty Shift
Replies
0
Views
62
ChetanPuri
C
R
  • Question
Count Date/Days
Replies
1
Views
214
Joe4
Joe4
R
  • Question
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
7
Views
195
Alex Blakenburg
A

Forum statistics

Threads
1,214,584
Messages
6,120,385
Members
448,956
Latest member
JPav

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top