Converting this raw data into something formulaic
 

Hi folks,

Been having real trouble sorting out a piece of data I'm working on, so I resorted to searching Google and I stumbled upon this forum! And what a great idea it is!

So, I've been tasked with converting some time/date data into a bell curve on Excel. Basically I need to take a months worth of data on a spreadsheet (albeit inputted with no formulas behind it - just completely bare text) and create something that shows the most popular time of day that people are using a specific piece of software. An example of a couple of lines of data is this:

2012-01-07 23:16:25 PST
2012-01-07 23:17:02 PST

That is all each line says. Think of that times by about 10,000 lines. I've got to convert that into a bell curve that makes sense.

So, anyone got any suggestions? I've got some Excel skills but I'm unfortunately no Guru!

Thanks :)

Converting this raw data into something formulaic
 

Are those date/times actual dates or just text...
Does the formula bar show a number like 40123?
Can you add a number to the date and not get an error?

If the answers are no, can you access the raw data and download it again?
That would provide the opportunity to download the data as dates not text.
-or-
You can possibly parse out the time portion with the Mid function...
=MID(B4,12,8) and use that.

What I am getting at, is actual dates; date/times could be used with the Frequency or Rank functions
to group the data and chart it.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles xl add-in: lists/removes unused styles & number formats) - free





"benallen105"

wrote in message
...

Hi folks,

Been having real trouble sorting out a piece of data I'm working on, so
I resorted to searching Google and I stumbled upon this forum! And what
a great idea it is!

So, I've been tasked with converting some time/date data into a bell
curve on Excel. Basically I need to take a months worth of data on a
spreadsheet (albeit inputted with no formulas behind it - just
completely bare text) and create something that shows the most popular
time of day that people are using a specific piece of software. An
example of a couple of lines of data is this:

2012-01-07 23:16:25 PST
2012-01-07 23:17:02 PST

That is all each line says. Think of that times by about 10,000 lines.
I've got to convert that into a bell curve that makes sense.

So, anyone got any suggestions? I've got some Excel skills but I'm
unfortunately no Guru!

Thanks :)




--
benallen105

Hi Jim, thanks for your reply. It's actually just purely text. There's absolutely nothing behind it right now unfortunately.

Converting this raw data into something formulaic
 

Hi Jim, thanks for your reply. It's actually just purely text.
There's absolutely nothing behind it right now unfortunately.

Assuming you do not have to convert that date/time to another time zone,
select the entire column (or column) of dates, press CTRL+H to bring up the
Replace dialog box, type this...

" PST*"

(without the quote marks)(notice the leading space) into the "Find what"
field and make sure the "Replace with" field is completely empty, then press
the "Options" button and make sure the "Match entire cell contents"
checkbox is NOT checked, then click the "Replace All" button and your values
should all get changed to real time/date values (which you can "cell format"
to look however you want).

Rick Rothstein (MVP - Excel)

Converting this raw data into something formulaic
 

Rick,
Nice one
'---
Jim Cone


"Rick Rothstein"

wrote in message
...
Hi Jim, thanks for your reply. It's actually just purely text.
There's absolutely nothing behind it right now unfortunately.

Assuming you do not have to convert that date/time to another time zone, select the entire column
(or column) of dates, press CTRL+H to bring up the Replace dialog box, type this...

" PST*"

(without the quote marks)(notice the leading space) into the "Find what" field and make sure the
"Replace with" field is completely empty, then press the "Options" button and make sure the
"Match entire cell contents" checkbox is NOT checked, then click the "Replace All" button and your
values should all get changed to real time/date values (which you can "cell format" to look
however you want).

Rick Rothstein (MVP - Excel)