Date and Number format conversion

[aganesan99]

Hello All,

I am having a report which has date and number in an unusual format like below.

Date Actual Output
1/6/2013 01-06-13
28-11-13 28-11-13

Amount Actual Output
5.249,00 5,249.00
1.528,00 1,528.00

The date should be in "dd-mm-yy" format and the Value should be in "##,#00.00" format.

I recorded a macro for both the formatting separately, but while using it I am getting error.

Please find attached my file for clear understanding of my situation.

Any help appreciated.

Thanks,
aganesan99

[Parth007]

Hi aganesan99,

You have to select the cell & then click the button.. If you select empty cell the macro will give error..

[aganesan99]

Hi Parth,

Thanks for your reply.

1. I face the below problems while using the "Convert 2 Number" macro.
This macro runs using "Find & Replace" option.
If I use the Find & Replace with option "within-WORKBOOK" and use this "Date Format" macro, it replaces in the entire workbook rather than the selected cells.
Every time I need to ensure this option "within-Sheet" is there before running this macro.
I need help in restricting it to replace only in the selected cells.

Find & Replace.png

One more problem is, if I select two columns, the macro shows error.

2. I face the below error while using the "Date Format" macro.
This macro runs using the "Text to Columns" option. When I select cells from bottom to top it is showing error.
Also if I select two columns, then also it is showing error.

I kindly request to help me!!!

Thanks in advance

aganesan99

[:) Sixthsense :)]

But your current Date Format code uses the TextToColumn method and not using the FindAndReplace method.

Please Login or Register  to view this content.

[aganesan99]

Hi Sixthsense,

Thanks for your reply and sorry it is vice-versa. Date format uses-Text to Column and Convert 2 Number uses Find & Replace.

Please help.

aganesan99