Avatar of Scott Fell
Scott Fell
Flag for United States of America asked on

EXCEL: Formatting As Date Not Working As Expected

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.



clienttest.xlsx


Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Scott Fell

8/22/2022 - Mon
SOLUTION
Rob Henson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Fell

ASKER
Thank you for the reply, I would have said something sooner but my home internet went down. I understand the value is stored differently than what you visually see. It is just that this has not happened before. I tried Brad's option, I also tried saving the file as a csv to make sure it was a date (it was) then Save As xlsx and the same issue.

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.
Martin Liss

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?
Scott Fell

ASKER
The problem is they are using another software package to export the data, then my web app uploads the excel sheet and imports to the database.  The problem is I need the date to be a string value like '1/1/2021'.

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott Fell

ASKER
I ended up just converting the number to date on the back end for this.

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.  
Robert Berke

Often an imported cell looks like a date but is kept as text.  And often it doesn't even have a leading apostrophe.
I have attached an example of such a file.

buggy date cell.xlsm


Author said
 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?
afterImport:
Dim ary
With Intersect(Columns(1), Columns(1).parent.UsedRange)
    ary = .Areas(1)
    .Areas(1) = ary
    .Areas(1).NumberFormat = "mm/dd/yyyy"
End With

Open in new window

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.


Scott Fell

ASKER
Thank you rberke,

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.  

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Robert Berke

Oops, i missed a lot of comment so disregard my previous post.

how about putting this near the end of your export routine
Sub fixit()
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")
Next


End Sub

Scott Fell

ASKER
Thank you rberke, as I mentioned to Martin already, I can't use vbs. They are creating about 20 exports from this proprietary program at a time. My web app has nothing to do with vbs or excel other than using it as a database to pull data from. It would be too cumbersome on the client to have this go through that for each excel file they create.

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.


ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.