Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Could anyone help me to provide the format as below. You can use Macro, if you want to format, else if any other easy step, tell me. I searched other threads and all refer to specific cells, but I need dynamic code.

S No  Column 1    Result    
1     07-09-2012  0912  (First Two Digit Month and Next Two Digit Year)
2     12-10-2012  1012

The result column is the desired output of mine.

One thing is important. This row may be 10 or 20 or anything. If I give the Date in the cell, then the result column should display the format as given in the Date column.

Hope you understood my requirement. Kindly let me know if you have any clarification.

share|improve this question

migrated from Oct 2 '12 at 11:42

This question came from our site for professional and enthusiast programmers.

Apply a custom number format of type "mmyy" to the cells. The underlying data is still a DateTimeSerial, only the display is affected.

share|improve this answer
thanks for your explanation. I tried the above said thing. I am not able to format the value based on the Column 1 Date. Could you please tell me in such a way that I could be able to format the Result value based on the Column 1 value? – Visu V Oct 3 '12 at 5:17
Visu - in column 2 eg cell B3 simply use a formula like =A3 to duplicate the value. Then use the custom format on cell B3. This assumes the cells in the first column actually contain dates, not strings of text that simply look like dates. – AdamV Oct 4 '12 at 20:17

the formula TEXT will give a string result based on the data you give.

Assuming that column 1 is a valid date in the system, then =TEXT(A1,"mmyy") would return the string you need

If column 1 is already text, then you will have to get the appropriate parts of the string with MID


will take the 4th,5th,9th and 10th characters out of the string, and produce the mmyy format you want

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .