Extrating charecters from a custom date format

jvanworthkhsd

Board Regular
Joined
Sep 26, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
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 1Polio 2
8/14/200111/5/2001
7/12/20028/30/2002
6/17/20023/12/2002
5/2/20027/11/2002
5/17/20028/15/2002
11/13/20022/20/2003
4/5/20026/11/2002
3/12/20025/22/2002
11/13/20021/13/2003
2/11/20024/12/2002

<tbody>
</tbody>
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 26, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows
Thats awesome....its so simple it confirms that my wife is right.........I'm a knucklehead.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
its so simple it confirms that my wife is right
Shhh...
I won't say anything if you don't!;)
 

jvanworthkhsd

Board Regular
Joined
Sep 26, 2008
Messages
78
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
63,302
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,987
Messages
5,834,723
Members
430,314
Latest member
Kym_beginner_in_VBA

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top