Log In
Register
Home
March 21, 2022
Offline
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
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi,
Can you tell us what date format you have set in your system? (Control Panel > Region)
If it's showing US date format, change it to UK date format.
Trusted Members
Moderators
November 1, 2018
Online
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
Offline
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
Offline
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)
