How to custom format a text

[bigscientist]

I want to enter a date in a cell as mm/yy where the slash "/" always populates automatically. How do I do this? The problems I'm facing are:

1) if I choose 00/00 or ##/## in Custom it converts my date into another fraction or decimal.
2) there's no option for a "mm/yy" under Date
3) the cell is currently a Text, which also has no customization option (ie., when entering the date you have to manually type "/").

Thanks so much!

[63falcondude]

You can use Custom mm/yy to format a date as mm/yy but it still has to be inputted in a way that Excel recognizes it as a date.

You will have to type in a slash or a hyphen between the month number and the year.

[Melvosh]

You could use a Custom Format of 00\/00. However, this will not be treated as a date.

[MrShorty]

If you enter a legitimate date serial number, then "mm/yy" should be a perfectly valid custom number/date format.

https://support.office.com/en-us/art...7-9c9354dd99f5
https://support.office.com/en-us/art...rs=en-US&ad=US

You are correct -- as text, you cannot use number formatting to change the display of the entered text. You can always use Excel's text manipulation functions to change the text string. If you enter '0119 in a cell, an adjacent cell could have a formula like =LEFT(cell,2)&"/"&RIGHT(cell,2) to insert a / in the middle of the text string.

A lot depends on exactly how you intend to use this -- what do you want to enter for data entry, what do you need the output to be, what are you going to use the output for, and so on.

[bigscientist]

I'll expand more on what I'm trying to accomplish. I'm having a cell to input the date. The final format of the date will be mmyy, in a text format, because I need to be able to search for that number (mmyy) in a larger string of numbers. Currently I have it set up as follows:

Input (cell is Text): 00/00
Next cell (hidden): removes the 3rd character or /
Next cell (hidden): searches for 0000

I was hoping to automate the / so it is always the 3rd character that gets removed. If the user types the / then everything is A-OK. However, if the user inputs the date as 0000, my calculation gets messed up.

I've tried using the mm/yy option under Custom, but when I type in for example 0321 (in an attempt to get 03/21, it comes out as 11/00 (or 11/16/1900)

I've also tried 00\/00 as someone suggested. It works well if you type the number without any slash. But if the user types the slash, again it gets messed up. I'd like the option to do either.


This is much like any online form that you might fill out on the internet, such as a credit card expiration, that automatically populate the /. I didn't know it would be this complicated.

[bigscientist]

Ok I figured out what to do but I need help to pull it off.

In a hidden cell, what would be the formula to search for "/" and if it's present then delete it? That should work.

[bigscientist]

SOLVED: used =SUBSTITUTE