Converting all date formats to one single standard format.

**booo** · 10-25-2010, 12:45 PM

Good Morning,

I have this report which contains dates in two different formats. I need a function to make sure that the date in any of these formats be converted to one standard format given by MM/DD/YYYY.

I have provided a spreadsheet with sample data of the format in which my date is provided. Kindly help me out on how this is done.

Thanks,
Booo

**TMShucks** · 10-25-2010, 01:09 PM

In cell C2, put:

=--B2

Format as you wish, mm/dd/yyyy, whatever, and then autofill down.

Regards

**booo** · 10-25-2010, 01:18 PM

I don't quite understand. Do you want me to type just "--B2" in C2?. And where do I provide the format?

Kindly state clearly cos I am a complete noob to excel.

Thanks

**MarvinP** · 10-25-2010, 01:23 PM

Hi booo,

It looks like some of your dates are Text and some are numbers (that excel likes better)
In C2 type = Type(B2) and pull down. It shows some are type 2 = text and some type 1 = numbers.

The function of DateValue will convert the Text to dates. Here is my method.
In C2 put this formula and pull down.

=DATEVALUE(MID(B2,4,2) &"/"& LEFT(B2,2)&"/"&MID(B2,7,4))

A fancier way to do this is to test if the cell is text and only do the datevalue then. Like:

=IF(TYPE(B2)=2,DATEVALUE(MID(B2,4,2) &"/"& LEFT(B2,2)&"/"&MID(B2,7,4)),B2)

hope that helps.

**booo** · 10-25-2010, 01:30 PM

Thanks Marvin. That solved my problem.

**TMShucks** · 10-25-2010, 05:29 PM

For clarification, although MarvinP has addressed your problem ...

I meant, type "=--B2" in cell C2 without the quotes. That is, an equals sign, two minus signs and the cell reference B2. The equals sign is because it is a formula and the two minus signs force the value to be numeric.

Or, if there aren't too many dates, you could just press F2 and enter in each date cell.

Both options worked with ypur sample data.

Regards

**MarvinP** · 10-25-2010, 07:14 PM

Hi TM,

I think the problem was his silly data was DD/MM/YYYY HH:MM AM/PM. I think the Day being first wouldn't allow =--B2 to work on my version of Excel (2010). It may have worked if I had a different date formatting as my default. That is why I went to DateValue and stripped out the parts. It will of course fail if he didn't have 2 day numbers or 2 month numbers in the string.

I wish there was a single standard for darn dates... But...