Count Days

[Greg]

Good day,

I am working on a project to model some work flows. I have a date column
that is entered as 8/15/2009, and formatted to show "Friday, August 15,
2009". Now, I want to count the number of rows for Monday, Tuesday,
Wednesday, etc.

However, since the underlying data is all in the 8/15/2009 format, I can't
seem to either sort or run calcuations on the data for what I need.

Any help is greatly appreciated!

Thank you in advance,
Greg

 

[Luke M]

Using the WEEKDAY function which gives Sunday = 1, Monday = 2, etc.

=SUMPRODUCT(--(WEEKDAY(A1:A10)=2))

This gives a count of how many cells in A1:A10 are Thursday. Note that
SUMPRODUCT can't callout entire column (A:A) unless using XL 2007. Feel free
to change the 2 to a cell reference, if desired.

 

[Greg]

Asked and answered!

=TEXT(A1,"dddd")

Then copy info, Paste Special "Values" and run calculations.

Sorry for the post.

Greg

 

[Gord Dibben]

See help on WEEKDAY Function.

Use a helper column to derive the day number from your dates.

=WEEKDAY(A1) copied down.

Count on that column.

=COUNTIF(A1:A100,1) returns a count of Sundays


Gord Dibben MS Excel MVP

 

[Ron Rosenfeld]

Good day,

I am working on a project to model some work flows. I have a date column
that is entered as 8/15/2009, and formatted to show "Friday, August 15,
2009". Now, I want to count the number of rows for Monday, Tuesday,
Wednesday, etc.

However, since the underlying data is all in the 8/15/2009 format, I can't
seem to either sort or run calcuations on the data for what I need.

Any help is greatly appreciated!

Thank you in advance,
Greg

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(MIN(Rng)&":"&MAX(Rng))))=DOW))

Rng is your column of dates (e.g. $A$4:$A$400)

DOW is the day of the week in which you are interested
1=Sunday
2=Monday
etc.

--ron

 

[Chip Pearson]

The following formula will return the number of Sundays in the list of
dates in A1:A30.

=SUMPRODUCT(--(WEEKDAY(A1:A30)=1))

Change the 1 to the appropriate day of week (1 = Sunday, 2 = Monday,
...., 7 = Saturday). The display format of the cells doesn't matter.

I have an entire page on my web site about working with days of the
week. See

http://www.cpearson.com/Excel/DayOfWeekFunctions.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Ron Rosenfeld]

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(MIN(Rng)&":"&MAX(Rng))))=DOW))

Rng is your column of dates (e.g. $A$4:$A$400)

DOW is the day of the week in which you are interested
1=Sunday
2=Monday
etc.

I forgot to add that for MIN(Rng) and MAX(Rng) you can substitute dates (or
cell references containing dates), representing the Start and End date that you
might want to consider for analysis.

If you just want to analyze the entire column, then the entire
ROW(INDIRECT(...)) can be replaced merely by Rng.

e.g.: =SUMPRODUCT(--(WEEKDAY(Rng)=DOW))
--ron