Changing a numerical field to a date field

I could really use some of your experience in coverting these fields into a date field. Would you please try to help me?

Example 91306 to 060913

I have encloed the file.

Thank you,

Originally Posted by MrSales

I could really use some of your experience in coverting these fields into a date field. Would you please try to help me?

Example 91306 to 060913

I have encloed the file.

Thank you,

let me get this clear.....91306 should be september 13th 2006? if you want it as a recognisable date use this formula in B1

=TEXT(A1,"00-00-00")+0

format B1 in required date format e.g yymmdd and then copy down column

VBA Noob I really appreciate your help but either I messed the formula when I was typing it in or it just doesn't work. I'm sorry to bother you again. Would you mind taking another look for me?

Thank you,

Mr Sales

daddylonglegs,

That is great for a quick fix but I would like to be able to sort the data in that row chronologically. Is there another way to do it?

Thank you,

Mr Sales

I don't know what you mean about "quick fix". My suggested formula is designed to convert data in A1 to a date in B1. If it works as intended then you can just sort the dates as normal.

Did it give you the correct dates?

VBA Noob,

I really appreciate all of your help with this. Just one more thing though, it is listing the month, year ,day, I would really like Year month day.

Thank you,

Mr Sales

Originally Posted by MrSales

VBA Noob,

I really appreciate all of your help with this. Just one more thing though, it is listing the month, year ,day, I would really like Year month day.

Thank you,

Mr Sales

If your

060913 is yymmdd then my formula above was giving the correct result. Don't really see why that didn't work

That's not the order it is showing up though. I would like to get year, month,day or if I could get each in their own cell then I could concatenate.

91306 09/06/13
62806 06/06/28
113006 11/06/30 Example 91306 to 060913
81606 08/06/16
81606 08/06/16
81606 08/06/16
122906 12/06/29
103106 10/06/31
30107 03/07/01
63006 06/06/30
20107 02/07/01
20107 02/07/01
33107 03/07/31
92107 09/07/21
122906 12/06/29

Thanks again,

Mr Sales

Well, my advice hasn't changed

If you use

=TEXT(A1,"00-00-00")+0 in B1 this will generate the actual date 13th September 2006.

Excel stores dates as serial numbers but you can format cell B1 however you like using

Format > Cell > Number and then using one of the inbuilt formats or your own custom format, so if you format as yymmdd the cell will show 060913 or if you format as yy/mm/dd it will show 06/09/13.

Whichever format you use you can sort the dates in chronological order simply using an ascending sort.

It depends on your default date format, if your regional settings give a default date format of m/d/y then that formula works in all cases.

I was assuming that the OP was in the US, in which case I presume that's his default date format but he isn't giving much away so we may never know.

If you just use

=TEXT(A1,"00-00-00")

then this will give the date in text format mm-dd-yy (I presume, assuming all dates are within 2006 and 2007) which you won't be able to sort chronologically.

Assuming that 91306 represents 13th september 2006 and that 62806 represents 28th June 2006 then this formula will give the correct date, whatever your default date settings

=DATE(RIGHT(A1,2)+100,LEFT(TEXT(A1,"000000"),2),LEFT(RIGHT(A1,4),2))

This should give you an actual date, in your default date settings, but, of course, you can format it however you like.

This also assumes that the dates are all 2000 or later