Hi,

I have a workbook with two columns. In the formula bar of one of the cells
i have 2005040101700 (YearMonthDateHoursMinutes), but what shows in the cell
is 2.00504E+11

When I try to format the cell as yyyymmddhhmm I get ########### and when I
put the cursor over the cell with ############### it says that negative dates
and times are shown this way. How can I make this work?

Thanks.

Allison

Allison,

I'm hoping that your actual value is different: 2005040101700 doesn't make sense:

year 2005 month 04 day 01 hour 01 minutes 70 seconds 0

Perhaps it's really

200504010170

year 2005 month 04 day 01 hour 01 minutes 70

Anyway, with that value in cell A1, this formula will convert it to a date/time.

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),0)

Format the cell for date time, or else you'll see something like 38443.0902777778

HTH,
Bernie
MS Excel MVP


"a_ryan1972" wrote in message
...
Hi,

I have a workbook with two columns. In the formula bar of one of the cells
i have 2005040101700 (YearMonthDateHoursMinutes), but what shows in the cell
is 2.00504E+11

When I try to format the cell as yyyymmddhhmm I get ########### and when I
put the cursor over the cell with ############### it says that negative dates
and times are shown this way. How can I make this work?

Thanks.

Allison

Right, I made a mistake. When entering your formula into the cell, are there
any spaces? I tried it and it didn't seem to work so I thought maybe I did
something wrong.

"Bernie Deitrick" wrote:

Allison,

I'm hoping that your actual value is different: 2005040101700 doesn't make sense:

year 2005 month 04 day 01 hour 01 minutes 70 seconds 0

Perhaps it's really

200504010170

year 2005 month 04 day 01 hour 01 minutes 70

Anyway, with that value in cell A1, this formula will convert it to a date/time.

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),0)

Format the cell for date time, or else you'll see something like 38443.0902777778

HTH,
Bernie
MS Excel MVP


"a_ryan1972" wrote in message
...
Hi,

I have a workbook with two columns. In the formula bar of one of the cells
i have 2005040101700 (YearMonthDateHoursMinutes), but what shows in the cell
is 2.00504E+11

When I try to format the cell as yyyymmddhhmm I get ########### and when I
put the cursor over the cell with ############### it says that negative dates
and times are shown this way. How can I make this work?

Thanks.

Allison

I also think I forgot to mention that these columns have thousands of rows of
data and in each one the date and time are different so am I correct in
assuming that it would not work if I selected all of the cells and entered
the formula?




"Bernie Deitrick" wrote:

Allison,

I'm hoping that your actual value is different: 2005040101700 doesn't make sense:

year 2005 month 04 day 01 hour 01 minutes 70 seconds 0

Perhaps it's really

200504010170

year 2005 month 04 day 01 hour 01 minutes 70

Anyway, with that value in cell A1, this formula will convert it to a date/time.

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),0)

Format the cell for date time, or else you'll see something like 38443.0902777778

HTH,
Bernie
MS Excel MVP


"a_ryan1972" wrote in message
...
Hi,

I have a workbook with two columns. In the formula bar of one of the cells
i have 2005040101700 (YearMonthDateHoursMinutes), but what shows in the cell
is 2.00504E+11

When I try to format the cell as yyyymmddhhmm I get ########### and when I
put the cursor over the cell with ############### it says that negative dates
and times are shown this way. How can I make this work?

Thanks.

Allison

In playing around with the document, I just noticed that if I double click in
the cell and then click outside of the cell, it reformats. Unfortunately, I
only know how to do this one cell at a time. Is there any way that I can do
this for the entire column?

Thanks.



"a_ryan1972" wrote:

I also think I forgot to mention that these columns have thousands of rows of
data and in each one the date and time are different so am I correct in
assuming that it would not work if I selected all of the cells and entered
the formula?




"Bernie Deitrick" wrote:

Allison,

I'm hoping that your actual value is different: 2005040101700 doesn't make sense:

year 2005 month 04 day 01 hour 01 minutes 70 seconds 0

Perhaps it's really

200504010170

year 2005 month 04 day 01 hour 01 minutes 70

Anyway, with that value in cell A1, this formula will convert it to a date/time.

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),0)

Format the cell for date time, or else you'll see something like 38443.0902777778

HTH,
Bernie
MS Excel MVP


"a_ryan1972" wrote in message
...
Hi,

I have a workbook with two columns. In the formula bar of one of the cells
i have 2005040101700 (YearMonthDateHoursMinutes), but what shows in the cell
is 2.00504E+11

When I try to format the cell as yyyymmddhhmm I get ########### and when I
put the cursor over the cell with ############### it says that negative dates
and times are shown this way. How can I make this work?

Thanks.

Allison

You can use the autofill button on the bottom of right corner of the selected
cell and drag it down as far as you want (for all those 1000 cells).

And select the column and format|cells (and apply the format you want).

a_ryan1972 wrote:

In playing around with the document, I just noticed that if I double click in
the cell and then click outside of the cell, it reformats. Unfortunately, I
only know how to do this one cell at a time. Is there any way that I can do
this for the entire column?

Thanks.

"a_ryan1972" wrote:

I also think I forgot to mention that these columns have thousands of rows of
data and in each one the date and time are different so am I correct in
assuming that it would not work if I selected all of the cells and entered
the formula?




"Bernie Deitrick" wrote:

Allison,

I'm hoping that your actual value is different: 2005040101700 doesn't make sense:

year 2005 month 04 day 01 hour 01 minutes 70 seconds 0

Perhaps it's really

200504010170

year 2005 month 04 day 01 hour 01 minutes 70

Anyway, with that value in cell A1, this formula will convert it to a date/time.

=DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MI D(A1,9,2),MID(A1,11,2),0)

Format the cell for date time, or else you'll see something like 38443.0902777778

HTH,
Bernie
MS Excel MVP


"a_ryan1972" wrote in message
...
Hi,

I have a workbook with two columns. In the formula bar of one of the cells
i have 2005040101700 (YearMonthDateHoursMinutes), but what shows in the cell
is 2.00504E+11

When I try to format the cell as yyyymmddhhmm I get ########### and when I
put the cursor over the cell with ############### it says that negative dates
and times are shown this way. How can I make this work?

Thanks.

Allison






--

Dave Peterson