Converting this raw data into something formulaic

[benallen105]

Hi folks,

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

So, I've been tasked with converting some time/date data into a bel
curve on Excel. Basically I need to take a months worth of data on
spreadsheet (albeit inputted with no formulas behind it - jus
completely bare text) and create something that shows the most popula
time of day that people are using a specific piece of software. A
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'
unfortunately no Guru!

Thanks :

 

[Jim Cone]

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"
<[email protected]>
wrote in message
news:[email protected]...

 

[benallen105]

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

;1175380']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 i
again?
That would provide the opportunity to download the data as dates no
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 th
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 & numbe
formats) - free





"benallen105"
<[email protected]>
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! An 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 mos 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,00 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

 

[Rick Rothstein]

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)

 

[Jim Cone]

Rick,
Nice one
'---
Jim Cone


"Rick Rothstein"
<[email protected]>
wrote in message