I have an excel file that is read by a python script. I am having problems formatting the dates. the dates are formatted as 9/25/2017 but my python programming can't read this for it expects the date to be 09/25/2017.
How do I make the excel file show extra 0 values to fill the format necessary?
use number format dd/mm/yyyy
sorry US format mm/dd/yyyy
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
that's what I did but it doesn't change how the date is displayed. the work around that I do is copy the whole column, paste it on a notepad, then copy from the notepad then paste to the excel file. after doing this the dates below,
1/22/2016
2/12/2016
3/14/2016
will now be shown like this
01/22/2016
02/12/2016
03/14/2016
Assuming you have A1 1/22/2016 then try
B1=IF(LEN(A1)=10,A1,"0"&A1)
Sounds to me like your dates are not really dates, but text looking like dates.
What you need to understand about dates and times in excel is...
a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Fri Mar 2017) is actually 42811
Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
So, with that said, do a quick test...
=isnumber(cell-ref)
if the answer is FALSE, you have Text and we will need to convert them
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
i note your profile says london which uses dd/mm/yyyy date standard but your using a US date format
does the cells actually react like dates in your excel?
if they are behavior like text than Shukla's solution should work fine
if they are acting like dates then
=Text(a1,"mm/dd/yyyy")
If this will always be a problem and you will be frequently using the said format..check your REGIONAL settings.
-> Control Panel
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
@FDibbins, how to do I convert them to number? I did what you asked me to do, they returned false.
@vlady, I have already changed my regional settings the same with my customers, with the expected date format.
If you did that and the answer was FALSE, they already are Text, we need to convert them to real dates (numbers). There a few ways too do that, but it would probably be best if you uploaded a sample workbook
Please Login or Register to view this content.
never mind FDibbins, I did this and it worked! https://support.office.com/en-us/art...1-c5bad0f0a885
thanks for schooling me on the details about dates
Good stuff, Im happy to help, and thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks