Left, Mid and Right Functions

probably because it's a real XL date and that is 39305 for that date....take a look at DAY, MONTH, YEAR

That's apparently an actual date formatted as mmmddyy.

	A	B	C
1	Aug112007	A1: Input	Format of A1: mmmddyyyy
2	Aug	A2: =A$1	Format of A2: mmm
3	11	A3: =A$1	Format of A3: dd
4	2007	A4: =A$1	Format of A4: yyyy

You are working with a formatted date value - the value for a data is - typically - a five digit number which may or may not have decimal values (the time portion of the Date/time value)

If you want to have the month, simply use

=M2

and format custom as MMM

For the day, use

=M2

and format custom as d

Or just use =DAY(M2)

For year, use
=M2
and format custom as yyyy

Or just use =YEAR(M2)

It's because M2 contains a proper date. i.e. 11 August 2007. It just happens to LOOK like Aug1107 because that's the way it's formatted. Don't confuse formatting with what the cell actually contains. In this case the M2 date number is 39305 which is why you get 393 when looking at the three left characters.

If you want to SEE the Day, month and year in three separate columns then just enter the formula =M2 in all three and format them as "mmm", "dd", and "yyyy" respectively.

However consider why you want to do this at all. If you're wanting to analyse a data set by date/month/year etc, then you already have the data in column M and you probably don't need any further splits.

J, there were several different suggestions. Which did you use?

Yes - just format at General instead of as a Date.

Dates are just numbers - the number of days since 12/31/1899. That is why 11 shows as 01/11/1900 if it is formatted as a date.