Setting specific date format VBA excel

0
votes

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)
1
excelvba
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
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
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
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
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

1 Answers

0
votes

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