htm date & time format

[Rob]

I have opened a htm data set in Excel 2003 that contains the date & time as
yyyymmddhhmm (the cell is formatted as general) and need it to display as
mm/dd/yyyy hh:mm.

For example 199704010053 in cell A1 currently displays as 1.99704E+11. I
would like it to display 04/01/1997 00:53.

I have tried custom formatting but end up with ###... and hovering the mouse
over the cell results in "Negative dates and times are displayed as ####"

What is the correct formula and cell formatting?

The htm data set dates & times are GMT and I need to convert it to PST/PDT.
Is this possible or would it be a seperate formula?

 

[Pete_UK]

Assuming that date/time is in A1, put this formula in B1:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,9,2),RIGHT
(A1,2),0)

and format the cell using a custom format of "mm/dd/yyyy hh:mm".

Copy down as required.

Hope this helps.

Pete

 

[Pete_UK]

I'm not sure what the time difference is between GMT and PST, but
let's say it is 5 hours, then you will need to add/subtract 5/24 to
the formula I gave you.

Hope this helps.

Pete

 

[Dave Peterson]

You can use this formula:
=--TEXT(A1,"0000\-00\-00 00\:00")

And give it a custom format of:
mm/dd/yyyy hh:mm

You can add or subtract any number of hours you want to this formula:
=--TEXT(A1,"0000\-00\-00 00\:00") - time(8,0,0)

(or whatever you need)

 

[Gary''s Student]

With the big number in A1, use:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),RIGHT(A1,2),0)

and format as you like. I think PST is about GMT-9, but I am not sure.

 

[Shane Devenshire]

Hi,

You can split this type of data by choose Data, Text to Columns and choosing
Fixed Width, Next, then click between the 8th and 9th character in the Date
Preview pane (between the date and the time) and a second one between the
10th and 11th character (between the hours and minutes. Click Next,
determine the destination and, in the data preview pane click in the first
column and from the Date drop down choose YMD. Click Finish.

Let's say the destination cell was A1, you get three cells for each entry
Date in column A, hours in B and minutes in C

In D1 enter
=A1+TIMEVALUE(B1&":"&C1)

Format this as you need

Cheers,
Shane Devenshire

 

[Rob]

Shane,

I was able to get one column to show the "mm/dd/yyyy", the second column to
displays "hh", but the third column that displays the "mm" is incorrect. In
this example the "mm" should be "53", but the result is "02", and the value
of the cell is 02/22/1900. I formatted third column as custom "hh". Did I
format the third column incorrectly?

 

[Rob]

Thanks!

Hi,

You can split this type of data by choose Data, Text to Columns and choosing
Fixed Width, Next, then click between the 8th and 9th character in the Date
Preview pane (between the date and the time) and a second one between the
10th and 11th character (between the hours and minutes. Click Next,
determine the destination and, in the data preview pane click in the first
column and from the Date drop down choose YMD. Click Finish.

Let's say the destination cell was A1, you get three cells for each entry
Date in column A, hours in B and minutes in C

In D1 enter
=A1+TIMEVALUE(B1&":"&C1)

Format this as you need

Cheers,
Shane Devenshire

 

[Rob]

Thanks!

With the big number in A1, use:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),RIGHT(A1,2),0)

and format as you like. I think PST is about GMT-9, but I am not sure.

 

[Rob]

Thanks!

You can use this formula:
=--TEXT(A1,"0000\-00\-00 00\:00")

And give it a custom format of:
mm/dd/yyyy hh:mm

You can add or subtract any number of hours you want to this formula:
=--TEXT(A1,"0000\-00\-00 00\:00") - time(8,0,0)

(or whatever you need)

 

[Rob]

Thanks for both replies!

Assuming that date/time is in A1, put this formula in B1:

=DATE(LEFT(A1,4),MID(A1,5,2),MID(A1,7,2)) + TIME(MID(A1,9,2),RIGHT
(A1,2),0)

and format the cell using a custom format of "mm/dd/yyyy hh:mm".

Copy down as required.

Hope this helps.

Pete

 

[Rob]

Shane,

Please disregard my reply that the "mm" column was incorrect. I re-checked
the formula and I had an error. I corrected the error and everything works
properly. Thanks!