

March 21, 2022

Hello again.
Please can someone explain:
All dates are entered as =DATE(yyyy,mm,dd). When I read these into a variable and then write them into a different workbook some dates change to 'American' style rather then the 'English' style that I am after. (Obviously! only dates where the day value is less than 13 are rearranged.) The dates affected are read into a string variable rather than a date variable because some entries in this field are text. Is this the cause of the problem? If so is there a cure?
Thanks in anticipation
Barry


Trusted Members
Moderators

November 1, 2018

Yes, using a string variable is the cause of the problem. When you write back to another cell, VBA will interpret the date using its default US locale. You can either use CDate(yourvariable) to coerce to a true date using your regional settings, or use the Value2 property of the cell when reading it so you'll get a Double rather than a Date value, which is not open to interpretation.

Answers Post


March 21, 2022

Thank you both for the replies. I had in fact gone through Excel setting to ensure UK. However the system setting was US??? Just now changed thet.
I will certainly try CDATE(). I'm not familiar with Value2 property - will do some searching.
Thank you baoth again -- really helpfull to have 'my own experts' on hand.


March 21, 2022

Hi. ...as a follow up... Checked out CDATE() and it advised that an error would occur if it recieved a wrong data type value is supplied, then we will get a type mismatch error. so I went to look at Value2 property. Just placed "2" at the end of my write statement:
strActionDate = Range("H" & intLoopCounter).Value2 ... voila!! problem solve. Wonderful thanks again.
1 Guest(s)
