Solved Date format in Excel

March 7, 2014 at 04:56:48
Specs: Windows XP
Hi

We take raw data from the source system called (ariba). In the raw data we have difference date columns like (Accounting date,Paid Date,Invoice Recevied data) etc.
The problem in the data is "few rows the date format would dd-mm-yy and few rows the date format mm-dd-yy.

Unable to figure out this issue.

Already i have tried with "text to columns" options converting this into correct date format still it is not working


See More: Date format in Excel

Report •

#1
March 7, 2014 at 07:52:41
✔ Best Answer
i have tried with "text to columns" options converting this into correct date format still it is not working

If the Text To Columns is not working
You can use the =DATEVALUE() function to convert the TEXT to DATE

But this will only work on Dates that are formatted in your Operating System Default Format.

In other words,
if you use the American style MM/DD/YYYY as your System Date,
then dates formatted in the European style of DD/MM/YYYY will not be changed correctly
and if you use the European Style the American style will not be converted correctly.

So what is your System Date formatted as?

I'm going to hazard a guess that you are using the European style date of DD/MM/YYYY and need to convert the American style.

For this we can use the =DATE() function where we first
split the date apart and then put it back together in the correct order.

With your American style Date in A1,
put this formula in B1:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

This is for a Date with a 4 Digit Year.

See how that works.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •
Related Solutions


Ask Question