Handling hhmmss time format

D

Deepak Tulsani

Hi,

I have a bit of a problem with the time formatting in excel, or rather
the handling of the same for the data that I have.

I have a big file, that has different counters separated by comma
(essentially a csv file). Here's a sample of a line in those files:

GPRS1,20120205,110000,gnctx,4,gprs,
3430,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

The third field (110000) in this example, is the timestamp.

GPRS1,20120206,004500,gnctx,
4,,65536,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
GPRS1,20120206,001500,gnctx,4,gprs,
790,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

GPRS1,20120206,020000,gnctx,4,gprs,
54004,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

Now I could happily plot the graph with these values, ONLY if Excel
would not strip the leading zeros from the timestamp in samples like
the latter 3 mentioned above. For a timestamp of 004500, Excel strips
the leading zeros and shows 4500. For timestamp 013000, it displays
the value as 13000. For a timestamp of 000000, it just shows 0! That
messes up the whole timestamps when I try to plot the graph, and just
cannot get to do what I want to.

I've tried many tricks, including setting the Custom format to
"number" of format "000000". That "displays" the field correctly, but
when I perform any operation, it still uses the originally displayed
value.

I don't understand why is Excel ignoring the leading zeros in the
first place, when the original value itself has leading zeros. Even
when I set the formatting to "Text", it still would strip the leading
zeros.

I've been breaking my head on this since last one week, but haven't
been able to get this work. Could someone help please?

Regards,
Deepak
 
V

Vacuum Sealed

Hi,

I have a bit of a problem with the time formatting in excel, or rather
the handling of the same for the data that I have.

I have a big file, that has different counters separated by comma
(essentially a csv file). Here's a sample of a line in those files:

GPRS1,20120205,110000,gnctx,4,gprs,
3430,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

The third field (110000) in this example, is the timestamp.

GPRS1,20120206,004500,gnctx,
4,,65536,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
GPRS1,20120206,001500,gnctx,4,gprs,
790,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

GPRS1,20120206,020000,gnctx,4,gprs,
54004,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0

Now I could happily plot the graph with these values, ONLY if Excel
would not strip the leading zeros from the timestamp in samples like
the latter 3 mentioned above. For a timestamp of 004500, Excel strips
the leading zeros and shows 4500. For timestamp 013000, it displays
the value as 13000. For a timestamp of 000000, it just shows 0! That
messes up the whole timestamps when I try to plot the graph, and just
cannot get to do what I want to.

I've tried many tricks, including setting the Custom format to
"number" of format "000000". That "displays" the field correctly, but
when I perform any operation, it still uses the originally displayed
value.

I don't understand why is Excel ignoring the leading zeros in the
first place, when the original value itself has leading zeros. Even
when I set the formatting to "Text", it still would strip the leading
zeros.

I've been breaking my head on this since last one week, but haven't
been able to get this work. Could someone help pleasing thee?

Regards,
Deepak
Click to expand...

Hi Deepak

As I am using 2010, it maybe a little different for earlier versions
having said that! I used your data and created a CSV File and used the
manual "Import from Text" button on the ribbon and selected the time
field column and changed it to TEXT and it imported with the leading zeros.

I'm fairly certain you can use VB to do this and extract the converted
time conversion value all at the same time.

As for converting it to time(hh:mm:ss), try this.

=LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)

Using your 3 time example, they should return the following:

00:45:00, 01:30:00 & 00:00:00

HTH
Mick.
 
D

Deepak Tulsani

Hi Deepak

As I am using 2010, it maybe a little different for earlier versions
having said that! I used your data and created a CSV File and used the
manual "Import from Text" button on the ribbon and selected the time
field column and changed it to TEXT and it imported with the leading zeros.

I'm fairly certain you can use VB to do this and extract the converted
time conversion value all at the same time.

As for converting it to time(hh:mm:ss), try this.

=LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)

Using your 3 time example, they should return the following:

00:45:00, 01:30:00 & 00:00:00

HTH
Mick.
Click to expand...

Ohh good heavens! That's great Mick, thanx a million for that. Atleast
now I can see the whole data in the correct format as was in the
original file. I'll try your formula along with probably the TIME
function, so that all data is directly converted to an "Excel
understandable" time format in one single go. Thanx a million for this
lead. I'll revert back with an update.

Regards,
Deepak
 
G

GS

Assuming that each record in the CSV is on a single line as apposed to
how it appears in my newsreader, have you tried opening the file via
VBA and formatting the data BEFORE dumping it into the worksheet?

Typically, values exported to a CSV are plain text and therefore are
'unformatted' as to data 'type'. This presents Excel's import wizard to
'guess' what the data type is. I find it's ALWAYS better to format
before committing any data to the sheet. In your case, the data needs
to be changed to "00:45:00" so it will be interpreted as time. Same
holds true for your 8-digit date format. IF it was mmddyyyy then you'd
have a similar issue for the first 9 months of any year. Likewise, if
it was ddmmyyyy you get same results for first 9 days of any month.

Maybe in your case you can get away with a custom format of "00:00:00"!
 
D

Deepak Tulsani

Assuming that each record in the CSV is on a single line as apposed to
how it appears in my newsreader, have you tried opening the file via
VBA and formatting the data BEFORE dumping it into the worksheet?

Typically, values exported to a CSV are plain text and therefore are
'unformatted' as to data 'type'. This presents Excel's import wizard to
'guess' what the data type is. I find it's ALWAYS better to format
before committing any data to the sheet. In your case, the data needs
to be changed to  "00:45:00"  so it will be interpreted as time. Same
holds true for your 8-digit date format. IF it was mmddyyyy then you'd
have a similar issue for the first 9 months of any year. Likewise, if
it was ddmmyyyy you get same results for first 9 days of any month.

Maybe in your case you can get away with a custom format of "00:00:00"!

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Click to expand...

Hi Gary,

Custom format "00:00:00" didn't seem to work for some reason. But the
solution that Mick provided, worked perfect! Its a little bit of
additional work for me to select the format, but as long as it works,
i'm happy with that extra bit!

Thanx a lot Mick and Gary for your feedbacks!

Regards,
Deepak
 
G

GS

Deepak Tulsani expressed precisely :
Hi Gary,

Custom format "00:00:00" didn't seem to work for some reason. But the
solution that Mick provided, worked perfect! Its a little bit of
additional work for me to select the format, but as long as it works,
i'm happy with that extra bit!

Thanx a lot Mick and Gary for your feedbacks!

Regards,
Deepak
Click to expand...

Mick's suggestion works because the cells are formatted as 'Text',
meaning you can't directly use the contents in calculations. My
suggestion was in the context of using VBA to read, revise, and write
the data to the worksheet. That's how I do it and dates/times display
correctly AND also work in formulas because Excel recognizes the values
as Dates and Time.
 
G

GS

Just for clarity, I set colA format to 'Time' as hh:mm:ss. I then set
colB format to 'Custom' as hh:mm:ss. I then selected 8 rows in those
cols and hit the keyboard shortcut to enter the current time
(Ctrl+Shift+:). Both columns displayed identically.

I then entered in colB the text "21:47:00" and it displayed 00:00:00
because Excel converted it to midnight 5/24/1900.

I checked my addins that use date/time as read from a CSV file to see
exactly how I got it to work. Here's what I found...

Target cells are formatted to 'Time' as "hh:mm:ss".

NumberFormat = "[h]:mm:ss;@"

The times are converted to TimeValue in 1 VBA project.
In this case, TIMEVALUE("21:47:00") returns 0.907639 as that's its
respective fractional value of a day. (Day=1.0)

When you dump 0.907639 into a cell that's formatted to 'Time' as
hh:mm:ss it displays 21:47:00

The other projects don't convert to TimeValue and so just enter the
time as "21:47:00", which is a literal string.

What's important is that the cells are preformatted and the data
contains the colons so that recognizes it as time (the entire string
has 8 characters). Basically, this is done the same way Mick suggests
but in VBA using the Mid$() function only...

vaData(i, n) = Mid$(vaData(i, n), 1, 2) & ":" _
& Mid$(vaData(i, n), 3, 2) & ":" _
& Mid$(vaData(i, n), 5, 2)

Dates are handle the same way using "/" as the delimiter.
 
Top