CODEWITHSUNDEEP
excelvba
Bharadwaj P
Bharadwaj P

Reputation: 1

Setting specific date format VBA excel

I have a unique problem for which I was not able to find a solution in the forum. I have an excel sheet where I take in date inputs through list down options. 3 cells have list down as Date(D1), Month(M1), Year (Z1) respectively. I have a button macro which picks up the value of the cells and also parallel through VLOOKUP the month name is converted to the month number and thus the output is a String with right format (DD/MM/YYYY). I used DateSerial combined with Format. So far so good!

Y1 = Format(DateSerial(Z1, M1, D1), "DD/MM/YYYY")

However now to convert this string to a date I am using CDate. If I have a regional date setting of US (format MM/DD/YYYY) then the CDate is converting the date to the local time format. I however need this (DD/MM/YYYY) format for future use. How can I get an output in specific format irrespective of the local date format? Is there any other solution? Thanks a lot in advance.

Edit: Y1 is declared as a String. When it was declared as Date, the date already appeared in regional format inspite of the format condition. I also tried the format function with the "" symbol as below but it gives me an overflow error.

Y1 = Format(DateSerial(Z1, M1, D1), DD / MM / YYYY)

Upvotes: 0

Views: 609

Answers (1)

Gustav
Gustav

Reputation: 55806

Set the date value (which has no format):

Y1 = DateSerial(Z1, M1, D1)

Then to Y1, apply the format you prefer, i.e.:

dd/mm/yyyy

Upvotes: 0

Related Questions