Format string as date and time for time series analysis

[MichaelNitsuj]

I want to find a way to change a string to usable date and time values so that I can run time series analyis on them. I have extracted printing records, each with "timestamps" from poster printers, but the data is unusable as it is. What I get is a string(well, thousands of them, in a column) like this:

20110308185235

which translates to:
yyyy mm dd hh mm ss
or:
2011 03/08 18:52:35

What I need are separate columns with date and time in usable formats. The year and seconds are irrelevent. I've used LEFT, RIGHT, LEFT/LEN, RIGHT/LEN to separate th data into columns of numbers and copied the values to a new column (formated as text so as not to lose any zeros). Built in date and time functions are no good. When I use them, they either do nothing at all to the numbers, or they translate the numbers to completely different numbers ("0301" to 8/1/2737 19:00) even when I use short date formats. Same thing with time: all I can get is 12:00 am for all values, or else nothing at all. I've tried making custom number formats, but that does nothing at all. I've tried recording a macro for changing the values, for example, "1120" to "11/20" but everything I do ends up not doing anything at all to the numbers, or else will do what I want, but outputs the same string as the first value I used for every value. One of many failed Examples:

Sub DateFormat()
'
' DateFormat Macro
'
' Keyboard Shortcut: Ctrl+D
'
Range("B2").Select
ActiveCell.FormulaR1C1 = "03/01"
Range("B2").Select
End Sub

If I use the shortcut on another cell, it applies the formula "03/01" as it was in the first cell, even if I change what cell is in the code.

If there is a way to insert "/" between characters for the date column, and ":" for the time column, or if there is any other way to transform a string like:
"20110308185235" to separate columns like: "03/08," and "18:52," consistently and accurately to thousands of entries, that I can duplicate monthly for time-based analysis of prints (like how many each hour, each day, which hours/days are busier, etc) I'd really appreciate it.

 

[Gerald Higgins]

Hi, welcome to the board.

Here's what I would do.

First of all, forget about the "/" and ":" characters for the time being.
You can add them in later with formatting.

Second, converting the components of your string to text format is probably a mistake, you really want to convert your components to numeric values.

Let's say the example you gave is in cell A1.

For the year

=left(a1,4)+0

should return 2011. The "+0" bit is to convert to a numeric value.
Let's say you put this formula in cell B1.

For the month

=mid(a1,5,2)+0

should return 3, and so on.
Let's say you put this formula in cell C1.
Don't worry about the difference between 3 and 03, that's not important for what you're doing here.

Use similar formulas to break out all the separate elements, so that you have cells for
Year
2011 (in B1)

Month
3 (in C1)

Day
8 (in D1)

Hour
18 (in E1)

Minutes
52 (in F1)

Seconds
35 (in G1)

To convert all this into usable time data, use

=date(year(b1),month(c1),day(d1))

and similarly for time.

Format the results as date or time, as required, which will make the "/" and ":" characters appear (if you specify them).

 

[CWatts]

For time, you can use the same method, but with the =TIME function which uses the following syntax: =TIME(hours,minutes,seconds)

You can then add that to your date which will allow you to easily do subtraction to find the difference between date/time combination.

 

[Gerald Higgins]

Sorry, one more thing.

If this approach works for you, there's no need to have all the individual formulas in separate cells.
You can combine them all into one formula, like this

=date(year(left(a1,4)),month(mid(a1,5,2)),........

 

[MichaelNitsuj]

This gives me the same problem. The left and mid functions separate the values well enough, but I had that part figures out. The problem lies in combining them into usable values with the right format. I did everything just as you said, but the output is wrong. As I was seeing before, the date and time functions translate the numbers into bizarre numbers. Example:

Time Stamp Year M Day Hour Min Sec Date Time
20110301172243 2011 3 11 17 22 43 1/11/1905 12:00:00 AM

Date and time fields should read: 3/11/2011, and 5:22:43 pm or 17:22:43.

I tried ALL of the date and time formats, to be safe. All of the date formats give me the same random, wrong date. All of the time formats give me either all 12:00 am in various 12 and 24 hour formats.

 

[Gerald Higgins]

Time Stamp Year M Day Hour Min Sec Date Time
20110301172243 2011 3 11 17 22 43 1/11/1905 12:00:00 AM

Check the formula you are using to extract the day.
It should return 1, not 11.
I think the formula should be

=MID(A2,7,2)+0

Are you by any chance using

=MID(A2,8,2)+0

by mistake ???

 

[MichaelNitsuj]

No, that's not the problem. Actually, I'd been using similiar functions the way you described them to separate the data, but the problem always got worse at the end when Excel fails to translate the data into the right date. After extensive research, here is what I've found: Excel is incapable of counting or using a basic calendar function, the way Outlook or MS Calendar might. Instead, when I ask Excel to turn the numbers into date format, it takes the number in the cell, and arbitrarily counts the number of days since Jan 1st, 1900. If you put the number "1" in a cell and format as date, it becomes 1/1/1900.

I thought Excel was used for advanced calculations...but the geniuses at Microsoft can't even count the number of days since AN ACTUAL ZERO, like 0 A.D., or perhaps just design and use AN ACTUAL DATE FUNCTION!

As if anyone anywhere at any time would record date information by counting from an arbitrary point in time, rather than simply writing the actual date. The "time" function is similarly broken, and makes even less sense: it uses the number as a measurement, counting the number of minutes since midnight. If the number is more than there are minutes in a 24-hour period, the cell just reads midnight.

 

[Gerald Higgins]

Hi Michael

With respect, I can't help wondering if we're misunderstanding each other in some way.

For a start, when you say that "if the number is more than there are minutes in a 24 hour period, the cell just reads midnight".

This is simply not true, at least in the following context.
The number of minutes in a day is 1,440.
If you enter 1,441/1,440 into a cell (this number being equivalent to 24 hours plus 1 minute) and format the cell as time, then the cell does not read as midnight.
If you format it as hh:mm:ss, it reads as 00:01:00, i.e. 1 minute after midnight.
If you format it as [h]:mm:ss, it reads as 24:01:00, again, 1 minute after midnight.
Or you can choose to format it as a combination of date and time, and again, it won't be midnight.

Or do you mean something else ?

Going back to your original problem, you mentioned this text string -
20110308185235
which should translate to 8th March 2011.

If you use the text formulas I mentioned, they will break the string out into its components, which you can then use to build a function that Excel works with as a date.

I can see that if you are dealing with dates before 1900 AD, then basic Excel may not work well for you, but for the examples you've given, it works fine. I use these date functions all the time, as do many other Excel users.

Is there something about your application that makes it more complicated than you've told us about so far ?