egafd.com

I know there are some really good computer experts on here so was wondering if anyone can show me how to do the following with Excel spreadsheets please?

I have a database and want to convert a column with times of phone calls in it on to a chart to show the busiest times of the day calls are received.

I can normally do charts but can't for the life of me remember how to brake down the time into hourly segments in the column. Sub totals just separates the times individually as they are in column order. (21:27:05, 22:40:36,10:00:51)

Sort works in that it gives me the hours but to do the chart I think I need them separated. Is there an easy way to do it so I can say 40 received between 9-10, 80 between 10-11 etc ?

I also want to create a macro that will change the date into just the day of the week the date represents. 07/12/2007 = Friday for example but I need to be able to do it from dates going back a while (years (2004) if possible).

Any help would be appreciated.

Cheers

Andy

Oh bugger I should know this as I'm confronted with Excel spreadsheets every day at work.

Have you tried the Help menu - or accessing that little paper-clip "animation"?

I don't know how to do it with excel as I worked with SQL... however it should be similar to the following.

Create a temporary result field that is an extract from the date and use this to work out the count.

In pseudo SQL it was something like "hits=%count(%hour(datefield))" as its been a few years now and you'd generate the breaks buy indexing on %hour(datefield).

Hope this helps a little in getting the brain kickstarted.

Jon Xx

Rather than try to explain the countif function

I've rarely ever been able to find the answer I am looking for using help but I am a numpty when it comes to computers.

Lots of links to click but never one relating to the question asked lol.

Thanks Jon

I was baffled before now I am completely confused lol.

Cheers Jacques

I still need help with converting the date into just the day using a macro if possible any ideas please?

Andy

In the help menu, have you tried typing 'converting a column'. You'd need to do a search for the word 'column' first, then in the sub-menu type 'convert'.

There is a formula you can use which needs to be placed in the format bar at the top of the page - but I can not remember what it is. The laptop I'm using now isn't the one I use at work.

Apparently dates is excel are just number of days counting from 01 Jan 1901.

So 07 Dec 2007 is the 39,424 th day after 01 Jan 1901.

Therefore, can you convert the date to number format in the macro, then convert it back to the date linked to a calendar back in the macro ?

Btw, have you tried highlighting the data in the coulumn, placing the cursor over it and right clicking the mouse? You then get the options page.