Extrating charecters from a custom date format

jvanworthkhsd

Board Regular
Joined
Sep 26, 2008
Messages
73
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>
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
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
73
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
55,974
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
73
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
55,974
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,572
Members
414,389
Latest member
MarkElla

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