Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

migrated from stackoverflow.com Oct 2 '12 at 11:42

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

2 Answers

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
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 '12 at 20:17
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.