Extrating charecters from a custom date format

[jvanworthkhsd]

In cell A1 I have a date 8/14/2001. The cell is formated as a date, Type: *8/14/2001. Which will convert into 37117 for a general format.

What I need is to seperate the 8 and 14 and 2001 into their own columns.

However, when I use this formula: =LEFT(A1,FIND("/",A1&"/")-1) it returns 37117.

Mind you I have multiple columns with hundreds of dates in the same format.

Polio 1	Polio 2
8/14/2001	11/5/2001
7/12/2002	8/30/2002
6/17/2002	3/12/2002
5/2/2002	7/11/2002
5/17/2002	8/15/2002
11/13/2002	2/20/2003
4/5/2002	6/11/2002
3/12/2002	5/22/2002
11/13/2002	1/13/2003
2/11/2002	4/12/2002

<tbody>
</tbody>

 

[Joe4]

Excel stores dates as numbers, specifically the number of days since 1/1/1900. That is why it shows 37117 when you choose the General option.
All functions are done to the underlying values, not the formatted values. So functions like LEFT are applied to the 37117 number.

However, there is a really easy way to get what you want. Use the MONTH, DAY, and YEAR functions.
=MONTH(A1)
=DAY(A1)
=YEAR(A1)

 

[jvanworthkhsd]

Thats awesome....its so simple it confirms that my wife is right.........I'm a knucklehead.

 

[Joe4]

its so simple it confirms that my wife is right

Shhh...
I won't say anything if you don't!

 

[jvanworthkhsd]

is there a simple way to get a leading zero for single digits...so
3/5/2001

will be 03 and 05 and 2001

 

[Joe4]

Well, if you want to just show the leading zero, you could simply apply a Custom Format of 00 on those cells.

If you need the leading zeroes to really be there (and not just appear visually), you can use the TEXT month, which works much like custom formatting.

For month:
=TEXT(A1,"mm")

For day:
=TEXT(A1,"dd")

Just be aware that returns a Text value. So if you are trying to do any mathematical calculations on it, they may not work the way you think they should.

 

[jvanworthkhsd]

got it...that will do