PDA

View Full Version : How to populate missing digits in a date cell or convert US to UK date format



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

mbarron
08-26-2009, 07:42 PM
Can you post the code you are using.

If the cells are date fields, you could assign the US formatting after you've pasted the date.

agarwaldvk
08-26-2009, 09:24 PM
Hi there

If the entry that you are reading is a numerical entry (number value) then it doesn't really matter - changiing the format will do it as has been suggested.

However, if the entry is a text value with "/" as the delimiting character, then you would have to get the character(s) - which could be 1 or 2 depending upon how has the date been entered - using one of the myriad of string functions available. Following this you can rewrite the three parameters that you have previously extracted and express them as dateserial and change the format of the cell to express the date in the US format.

Hope that helps.


Best regards


Deepak Agarwal

bananatang
08-27-2009, 04:11 AM
Thanks to all who replied. I will try and report back on outcome.