How to Create a Chart using days of week, from a series of dates

**Dan86** · 01-28-2013, 01:28 AM

So I have a column of dates (mm/dd/yyyy) numeric format (needs to stay in this format for other formulas and functions being used.)

The sheet has over 1000 entries, over several months, and I am trying to create a graph, that shows how many entries we input on which days of the week.

My Date Column is A.
Some days have 0 entries, some have 20+.

I'd like to end up with graphs showing as a %, what day of the week Monday - Sunday, we have out entries.

Any advice would be helpful.

Cheers

**FDibbins** · 01-28-2013, 01:42 AM

Hi Dan.

Without seeing what you are working with, give this a shot. In a helper column, enter this, copied down (I used C, so adjust the references in the countif() function as needed)...
=WEEKDAY(A1,2) this will give you the days of the week, with Mon = 1 and Sun=7

Then make up a small table with 2 columns. In the 1st (I used E, adjust as needed) enter 1 to 7, and in the 2nd, use this, copied down...
=COUNTIF($C$1:$C$44,E1)

You can then (if you want) add weekday names, and then chart based on the table

Let me know how you make out?

**Dan86** · 01-28-2013, 03:41 AM

Thanks FDibbins, works like a charm!

Is is possible to put the =WEEKDAY(A1,2) into a formula? remove the need for a helper column?

I'm using COUNTIFS for many things in sorting the mass amounts of data.

so right now my COUNTIFS has 3 criteria, criteria_range1 being my helper column, and criteria1 being my days of the week (1-7 respectively)

Is there a way for me to turn criteria_range1 from the helper column into the "=WEEKDAY(A1,2)" code?

If not is this is working, so no real need to worry.
Thanks!