Setting specific date format VBA excel

Question

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)

Thank you for reply. If I store as Date (here for example Dim Y1 as Date), it still stores the date in the MM/DD/YYYY format based on the local format. — Bharadwaj P
– Bharadwaj P, Commented May 17, 2021 at 13:02
Hence whenever you need the specific format, use Format... on a Date variable. "However now to convert this string to a date I am using CDate" ... why? That's going backwards. You had a Date with DateSerial. — BigBen
– BigBen, Commented May 17, 2021 at 13:03
In other words, a Date is a floating-point number. It is displayed to you "according to the short date format recognized by your computer." If you want a consistent format, then use a String, as you are doing. If you want a Date variable, then remember that it's just a number. — BigBen
– BigBen, Commented May 17, 2021 at 13:30
That's your problem. Don't compare against a String version of the date. Use a Date variable and for example the Month and Year functions. — BigBen
– BigBen, Commented May 17, 2021 at 13:44
Could you share the complete code so we can see why you need to convert the date to a string in the first place? It seems unlikely that you need to do this at all. — VBasic2008
– VBasic2008, Commented May 17, 2021 at 15:07

Gustav · Accepted Answer · 2021-05-17 14:44:45Z

0

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

answered May 17, 2021 at 14:44

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Setting specific date format VBA excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related