I am getting an Excel file that is attached in the question below. I have a program that imports this sheet and I am having trouble with the date. The first row of data is coming through as, 14784 and the expected value is 6/22/1940.
The data is being exported from another program I don't have control over by the user. The user then imports the excel file to my program. I have tried to format the column as shown
and I am still getting the date passed as an integer. I am doing the same from other excel files and there is no issue, it is just when exported from this one program. The people that are doing this are not going to remember to format this each time.
I am looking for something easy that will format the underlying data as a date and not just visually.
I may just attack this on the server side.
I can't really do the macro or vbs idea because they are going to be doing about 20 of these experts on a regular basis and I wouldn't expect them to go through the trouble of all this work when the other data I get in Excel formats for them is working as expected. This is very odd.
I can't really do the macro or vbs idea...Would it help if my code ran automatically when the data in the worksheet is changed?
If there is not an easy fix for this that they can do easily, then I may look into just having them upload as a csv or converting the number on the backend.
I will leave this open though and if anybody has an idea. It is odd because I have been importing dates from other excel sheets.
I have attached an example of such a file.
buggy date cell.xlsm
I have a program that imports this sheet .and he said
The people that are doing this are not going to remember to format this each time.if "the people" use the Author's program to do the import then clearly that program should be changed.
If the program is in vba, the following lines might fix everything?
If "the people" don't run the program then it is still the Author's responsibility to fix the process. Again the above code might solve the problem.
With Intersect(Columns(1), Columns(1).parent.UsedRange)
ary = .Areas(1)
.Areas(1) = ary
.Areas(1).NumberFormat = "mm/dd/yyyy"
I have created 4 different import templates where the data goes from one of 4 excel layouts to my web app. 2 of the other 3 have the month, day and year separated and I am just concatenating -> making sure the concatenation is a valid date -> import to db or reject. The third source the dates are in a format like 2021-10-15 and I suspect you are correct that that column is a string and not a date
In this case, the data is formatted as 10/01/2021 and stored as an integer.
I don't want to put any extra work on the client using this, so you are correct, it is up to me to deal with it on the back end and as I mentioned just prior to your post, that is what I am doing.
On the import, I have a form where they can choose which of the four import templates they are using and that determines how I am handling the data.
Thank you all.
how about putting this near the end of your export routine
Dim Cell As Range
For Each Cell In Intersect(Columns(1), Columns(1).Parent.UsedRange)
Cell.Offset(0, 1) = "'" & Format(CDate(Cell), "MM/DD/YYYY")
The path of least resistance is to handle the integer on the backend and convert to a date. I am doing something similar but it is a function in PHP with a date class https://github.com/PHPOffice/PhpSpreadsheet/blob/master/src/PhpSpreadsheet/Shared/Date.php using the excelToDateTimeObject method.