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