1

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.

Improve this question

2 Answers 2

Reset to default
5

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

Improve this answer
2
  • 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, 2012 at 5:17
  • 1
    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, 2012 at 20:17
0

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

=MID(A1,4,2)&MID(A1,9,2)

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

Improve this answer

You must log in to answer this question.

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