bananatang
08-26-2009, 07:21 PM
Hi,
Need help here please.
I have a situation when i execute a macro which copies cell data from one sheet to another.
The problem i have is that it convert a date cell value format from UK (DD/MM/YY) into the US format (MM/DD/YY)
I can overcome this with a formula such as =DATEVALUE(MID(H2,4,2)&"/"&LEFT(H2,2)&"/"&RIGHT(H2,2)), however i have noticed that the dates which are not in double figures do not show a 0 digit. i.e if date is 05/03/09 the macro would paste 3/5/09. Therefore i am not able to use this formula.
What i would like is for a macro to convert the the date from US to UK format.
OR
for a macro that will check the date cell and populate the Day or Month with a 0 if applicable. i.e if DD/MM portion of the date has only a single digit to change the date accordingly to a 2 digit DD or MM. i.e. If the date was 9/6/09 to change to 09/06/09.
i have been trying various method to try and resolve this but no joy.
BT
Need help here please.
I have a situation when i execute a macro which copies cell data from one sheet to another.
The problem i have is that it convert a date cell value format from UK (DD/MM/YY) into the US format (MM/DD/YY)
I can overcome this with a formula such as =DATEVALUE(MID(H2,4,2)&"/"&LEFT(H2,2)&"/"&RIGHT(H2,2)), however i have noticed that the dates which are not in double figures do not show a 0 digit. i.e if date is 05/03/09 the macro would paste 3/5/09. Therefore i am not able to use this formula.
What i would like is for a macro to convert the the date from US to UK format.
OR
for a macro that will check the date cell and populate the Day or Month with a 0 if applicable. i.e if DD/MM portion of the date has only a single digit to change the date accordingly to a 2 digit DD or MM. i.e. If the date was 9/6/09 to change to 09/06/09.
i have been trying various method to try and resolve this but no joy.
BT