Manipulating Dates and Format

**pdiminski** · 02-16-2010, 02:07 PM

I am attempting to analyse some sales data, what I want to do is create some stats re which days of the month are the most popular. So of a very large list I need to take the data which is displayed as below and reduce it to the day of the week ak the first part before the "/". The rest is surplus.

So It would be good to end with a column with "5" or "day of month" then I'll filter and count them all up so I know that lets says 205 of all sales happen in the first few days of the month. Maybe later even the times of day but I am getting ahead of myself.

Can anyone please help? maybe I am suggesting a more long winded way of doing it then is required.

P
05/11/2009 11:12
05/11/2009 11:04

**DonkeyOte** · 02-16-2010, 02:10 PM

Add your day of month field - eg:

B1: DOM
B2: =DAY(A2)
copied down

Then use a Pivot Table / Chart.

Use your Day Field (DOM) as Row Label and Date Field as Data Field set to Count.

You will now get a Matrix output giving you frequency by Day of Month.

If needed post a sample file.

**pdiminski** · 02-27-2010, 10:33 AM

Thanks but I am afraid I am confused.
If A1 says 01/01/2010 how do I reduce this to DOM?

**darkyam** · 02-27-2010, 03:28 PM

You would use DO's formula in a helper column and filter on the helper column. See attached example.

I didn't do the Pivot Table part for you. DO's instructions should be fine for you on that.