Date formats/values change when file is opened programmatically

vba numberformat date
vba date to string

I have .csv file which is a time series of daily data, with several data points associated with each date.

When I manually open the file, the dates open correctly, as the date format dd/mm/yyyy.

When I open the file programmatically, the dates up to the 12th of each month are opened as mm/dd/yyyy, although the format remains dd/mm/yyyy (e.g. the 1st of July 1983 (1/7/1983), would be opened as the 7th of January 1983 (7/1/1983) - this isn't just a formatting issue, the Julian Date (days since 1 Jan 1901) associated with these dates also changes), and the dates after the 12th of each month are opened correctly, although as text rather than a date.

The data coming in as text is not an issue, however, the dates changing as soon as the file is opened is problematic. I could try to import the entire .csv file as comma delimited text rather than opening the file, however, it would be easier and faster if I could stop the dates from changing when I open the file.

Flder = InputBox("Copy and Paste Folder path here:")

Set FSO = CreateObject("Scripting.FileSystemObject")
Set SourcePath = FSO.GetFolder(Flder)

For Each File In SourcePath.Files        
    Workbooks.Open (File)

    FlNm = File.Name

    StrtCol = Workbooks(FlNm).Worksheets(1).Range(Cells(4, 1), Cells(4, 30)).Find ("Mean").Column

    Workbooks(FlNm).Worksheets(1).Range(Cells(1, 1), Cells(60000, 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("A3"))
    Workbooks(FlNm).Worksheets(1).Range(Cells(1, StrtCol), Cells(60000, StrtCol + 1)).Copy (Workbooks("Find Water Years V2.xls").Worksheets(1).Range("B3"))

    Workbooks(FlNm).Close
Next

The problem seems to occur at the line Workbooks.Open(File).

Since the question has already been answered by the OP in the comments but not posted as an official answer I'll put it here in case someone else misses it like I did.

workbook = workbooks.Open(filename, Local:= true)

By setting Local = true uses the local machines date format rather than assuming mdy so in locales such as Australia (dmy) it'll change the behavior of Open()

How to: Specify Number or Date Format for Cell Content, The image below shows the different formats used to display date and time values in cells (the workbook is opened in Microsoft® Excel®). Under format click the Change date and time formats link. Use the Short name drop-down menu to select the date format you want to see in the Taskbar. Use the Short time drop-down menu to select the

I could try to import the entire .csv file as comma delimited text rather than actually opening the file, however, it would be easier and faster if I could stop the dates from changing when I open the file.

It is still fast if you open it as CDT. You simply use .OpenText instead of .Open. Rest of the code remains same :)

Try recording a macro and you will see that the code looks somewhat like this.

Workbooks.OpenText Filename:= File, _
Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
TrailingMinusNumbers:=True

Java 8 Recipes, The files that the Properties object read are in the form of [name]=[value] and If you need to store values in other formats, you need to write to and read from a string. the program (the user directly opens a text editor and changes the values), different properties programmatically, you call the getProperty(String) method,  How to: Specify Number or Date Format for Cell Content. Mar 20, 2020; 3 minutes to read; You can specify how to display numeric values in cells by applying number formats.For example, a number can appear in a cell as a percentage, decimal, currency, accounting, date or time value.

I found that user2981639 had the right idea, but I got a syntax error with 'Local: true' so I used 'local:=True' instead

This problem was driving me nuts because I also had 2 memo fields in the csv file. If I tried to format the dates in any way the memo fields would not import correctly, they would either be truncated to 255 characters or any embedded CRLF characters would cut the record up into pieces depending on how many there were.

Thanks guys for posting

How to Convert MM/DD Date Format to DD/MM in Excel with Report , Excel does not have a function call to programmatically change the Locale Excel chooses the default regional date format (i.e. MM/DD/YYYY, To use a snippet, right-click the image, save it to your computer, and drag the file onto to Front.vi function to open Excel once the worksheet has been edited. The problem that I'm having is that dates are just written in columns as strings and not initialized with the proper cell format. Is there any way to format the date without any 3rd party software by just setting the value of a cell with a string?? Such as: sheet[i,j].Value = "{:DateTime}"+dateStringProperlyFormatted

Here, you see a working code with example. This problem frustrated me as well for a while. I was opening a .txt file in excel and the date I had use for was converting from dd-mm-yyyy format to mm-dd-yyyy format. Below you see a solution. It lay in the last command (,Local := True). See below. Hope it helps.

Note that while trasferring .txt file to excel use File Origin as "xlWindows:
    Workbooks.OpenText Filename:= _
    ThisWorkbook.Worksheets("Reporting").Cells(3, 6), Origin:=xlWindows, _
    StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(49 _
    , 1), Array(50, 1), Array(67, 1), Array(80, 1), Array(93, 1), Array(106, 1), Array(119, 1)) _
    , DecimalSeparator:=",", ThousandsSeparator:=".", TrailingMinusNumbers:= _
    True, Local:=True

NOTE: rest above settings for array and decimal separator are my work specific - may not apply to you.

Date format that's convert a CSV text file by using VBA is not correct , Date format is not correct when you convert a CSV text file in Excel by using a VBA macro For more information about this change, read this blog post. to convert a Comma-Separated Values (CSV) text file into a Microsoft  Manipulate the Visio file format programmatically. 04/17/2019; 39 minutes to read +2; In this article. Learn how to create a solution in Visual Studio 2012 to read the new file format package in Visio 2013, select parts in the package, change the data in a part, and add new parts to the package.

I am also struggling to find an answer. However what resolves this problem is.:-

Local should be true. and workbook close save changes must be false. That should work.

How to change Excel date format and create custom formatting, Select the cell with a date in Excel, press Ctrl+1 to open the Format Cells window and If you are dealing with date and time values and you'd like to convert time to Once you've got a file full of foreign dates and you would most likely want to hi sevetlana how to inserting date use utc format in c# code. Stop Excel from Converting Text to Number or Date format when Importing a CSV file Option 1: Rename .csv to .txt and then open in Excel. To prevent Excel from automatically changing the data format to number/date format, you can rename the .csv file to .txt. Then open the .txt file from the File menu in Microsoft Excel.

How do i get excel to stop changing my date format, Select all your dates (from B6 to AF6) Open the Format Cells dialog box (Ctrl+1 or Instead of opening the exported file directly in Excel, save the file to your computer and The list of values in the Shipment Order Date column appear. Just changing the formatting to “Normal” doesn't remove the programmatic features of  You can easily change the format of any cell in an Excel worksheet to let the application know what type of data to expect. Two often used formats are the date and time. In fact, inserting the date and time into Excel is so common, Microsoft built in a few shortcuts and functions to help you add these types of data into your worksheet.

Long Date Format, The format display is: How to Change Date Format in Windows 7 This tutorial will show MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. C# DateTime class in C# provides properties and methods to format dates in Many times you have to create file paths with timestamp using SSIS Expression. To access the date and time format settings in Windows 7, click the Start menu button and enter “change the date” (without the quotes) into the Search box. Then, click the “Change the date, time, or number format” link in the list of results. This opens the Region dialog box we discuss in the next section.

Personalize the system date format, Personalizing the date format does not change global settings or impact the way other users see date values. Note: It is recommended that you use a yyyy format-​  We have an asp.net c# program that reads a sheet from an Excel file and writes it out in a new sheet (also adding one column if data come from a Sql Server table). Issue: in the new sheet the data is not formatted as we want. For example we want date without time and left-aligned, but they're formatted with the time and right-aligned, etc.

Comments
  • Can you define "open programmatically"? Do you mean via Excel automation? Or directly in VBA code? There's no way "opening the file" could change any content; it's something other than that, but you've posted nothing pertinent to that in your question. Opening a .csv file does not do anything to change the content in itself (for instance, in Notepad); post some code or other info that shows how you're reading it and what you're doing with the text you read. Otherwise, I'd suspect your question is going to be closed as "not a real question". :)
  • Sorry for the lack of clarity on the one Ken, code below.
  • Have to agree with Ken here. There is an underlying cause. What does the file look like in notepad at that point, how are you opening the file? Is it being pulled into a template. Pasted?
  • The file is opened directly into excel using Workbooks.Open(File) (see the above code that I just pasted). When the data is opened in Notepad the dates are correct, in dd/mm/yyyy format, the same as when the file is opened manually.
  • so, rep isn't high enough to answer my own just yet. The solution was to set the Local variant of Workbooks.Open to true, forcing it to parse in the data using the Local settings, rather than the default (US). If one of you want to answer the q feel free, otherwise I'll throw up the answer later this afternoon or tomorrow morning.
  • Thank you for clarifying this, as just had the same problem. While is obvious there is a local issue, is not as obvious what the fix is (unless you know each little function parameters out of your head). I can't see a reason why that option is not true by default.
  • Me either, given that when you open a file interactively it behaves as if Local:=True so I agree it should be the default
  • My experience is OpenText doesn't work if the dates aren't formatted mdy
  • No. But you can enforce importing as a text, manipulate the text to have correct unambiguous format (preferable ISO, which is YYYY-MM-DD) and apply CDate on such manipulated string.