Formatting: Date imported from Oracle

Hi everyone,

I'm generating a report on an oracle based software which then I export to Excel. Unfortunately the dates come out very messy. I've included an .xls file with the dates. They are untouched. As you can see, some are left aligned some right aligned. Nevertheless, all dates are of the format dd/mm/yyyy with zeros (0) automatically omitted - this is how they're shown on the Oracle report, I've changed nothing.

I want all the dates on column B to have the same format as the date on cell D3. Of course, the format must remain dd/mm/yyyy.

Please feel free to ask me questions if any of this is confusing.

Thanking you all in advance.

Regards,
Besian

The problem is that the dates in that first column are actually in two different formats. This formula will convert them both to Excel's raw date reference.

Please Login or Register  to view this content.

Now copy that formula down, then format the cells for DATE > 14-Mar-2001.

=============
If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

As stated, the problem is in your display, not the formula. Highlight the entire row and select Format > Cell > General and you will see what I mean.

Cell B4 is actually storing Excel's base numeric value for January 1, 2008, which is the value 39449. But B3 is storing a text string 29/01/2008, which in your mind is January 1, 2008. They are completely different types of data.

My formula is designed to take the non-numeric cells (like B3, B5, B6, etc) and convert them TO numeric values where you can use any Date formatting you want. That part is working correctly.

But the cells that are already numeric it just brings over, there is nothing to convert.

I don't have a solution for your date cells that are already in date format (like B4) but are simply wrong? Basically, any of the cells in your range that have a value of 12 or less in the first value are being interpreted by your system dates in MM/DD/YYYY format and Excel is storing the numeric date value for those cells.

Since those numbers are all over the map, you will need to look at the process by which those dates are first coming into your system. Are you "importing" something? If so, you need to flag that entire column of data as TEXT before Excel has a chance to mess it up.

I can look at the raw data if you send me a sample, but that's going to be the final step, keeping Excel from converting your DD/MM/YYYY data into dates all over the calendar.

Well, I spoke to soon, took a couple more hoops to jump through, but this formula ow deals with your incorrect numeric values.

Please Login or Register  to view this content.

That underlined bit converts your numeric value to TEXT, then back to numeric value, but this time the numeric value you want, which is totally different than what Excel thinks it is to start with.

Copy that cell down.

Originally Posted by daddylonglegs

You can convert the dates in situ using text to columns feature, i.e.

Select column B

Data > Text to Columns > Next > Next > under "column data format" select date and format DMY > Finish

As usual, the best solutions are the simplest. And I learned something new about TextToColumns dealing with a single column. Thanks.