Extrating charecters from a custom date format

jvanworthkhsd

Board Regular
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>

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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)

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

its so simple it confirms that my wife is right
Shhh...
I won't say anything if you don't!

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

will be 03 and 05 and 2001

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.

got it...that will do

Replies
5
Views
172
Replies
7
Views
541
Replies
16
Views
838
Replies
11
Views
275
Replies
15
Views
674

1,214,684
Messages
6,120,874
Members
448,993
Latest member
InquisitiveFrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back