CODEWITHSUNDEEP
exceldatetimecharts
JS1986
JS1986

Reputation: 1950

Data Chart to show times of day in Excel

I have a data set where by i am required to show the sum of calls for a date and their call times also.

Here is an example of my data set for a few days for one extension

6/24/11 7:43:38 AM 6/24/11 8:10:23 AM 6/24/11 8:16:54 AM 6/24/11 8:20:45 AM 6/24/11 4:47:06 PM 6/25/11 12:38:43 PM 6/25/11 5:38:10 PM 6/26/11 7:32:53 AM 6/27/11 5:40:32 PM 6/28/11 3:46:05 PM 6/29/11 7:09:21 PM 6/30/11 5:59:54 AM 6/30/11 1:21:28 PM 6/30/11 5:59:00 PM

Is it possible to put this into a chart whereby we can see a sum of calls for a given date? If that makes sense.

Thanks,c

-=

I found the following post at Excel Timesheet

this fixed my problem.

Upvotes: 2

Views: 2934

Answers (2)

JS1986
JS1986

Reputation: 1950

I found the following post at Excel Timesheet

this fixed my problem.

Excel Timesheet

Upvotes: 0

Tom
Tom

Reputation: 568

Easiest way is to have a number of helper columns:

Example: Column A through C contain the raw data for Extension 1-3 Column D through E contain the 'truncated' version of the time stamp into days Column H through K would contain the frequency of calls for a given day and extension

Put the formula in E in order to get the truncated day, and expand it over the columns E-G

=DATE(YEAR(A3),MONTH(A3),DAY(A3))

Put the starting day of interest in Cell I2. For Cell I3 enter the formula

=I3+1

so you will have automatically incrementing days in that column, this is your 'bin' for the histogram you're about to make.

Count the number of times the value in E is found for a day in your bin In J ( column counting calls per day for extension 1 ) enter the formula:

=COUNTIF(E$3:E$16,$I3)

drag this formula out.

Now you should have the data for your histogram/plot in columns I through L.

example screenshot

Upvotes: 3

Related Questions