A file that I converted from PDF to Excel has dates that show in column as ( example)
08/12/2020
04/11/2020
However in the task bar they show as 12/8/2020 and 11/4/2020
The correct date is whats in the columns 8/12/2020 etc
When I format as dates they reverse to 12/8/2020 etc.
What do I need to do to correct this. Thanks
windows 10, excel 2017
if you change to format general - do you get a number
also see the yellow banner a sample would help
As you in US , I suspect you want MM/DD/YY
Is the PDF formatted as US dates
Excel will not know if 8th of Dec OR 12th Aug
How did you import ?
if you do =DAY(Cell With Date)
What day number does it give you ?
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Imported from PDF File which shows the correct dates before saving to excel. When I did a General format the number is 44173 and when I did =day(cell #), I got 1/8/1900.
so the DAY = 8
as it is a number its imported as a date
44173 = 8th Dec 2020
so the date is correct
I suspect in the formula bar its showing your system date format, from the PC Settings - NOT EXCEL , as far as I know , thats the case
I'm in UK
If i change the format of the date cell to MM/DD/YYYY
then I get
12/08/2020 MM/DD/YYYY
BUT in the formula bar, i get my default system format 08/12/2020 = DD/MM/YYYY
Last edited by etaf; 01-11-2021 at 01:42 PM.
The correct date is 8/12/2020 but the formula bar shows it as 12/8/2020
Here is my converted excel file from pdf. They all appear corrct in the cell, but not all show same in Formula Bar. Thanks
I can send pdf file also, but not sure that would help.
When you say one is correct and the other is not, exactly what do you mean? Both displays represent 8 dec 2020.The correct date is 8/12/2020 but the formula bar shows it as 12/8/2020
Looking down column D, I see that some are using a number format code of dd/mm/yyyy (D2:D4, for example). Others are using a format code of mm/dd/yyyy (D5:D10, for example). Perhaps this is your concern -- that some entries in column D are formatted to display d/m/y and others are formatted to display m/d/y? If so, simply select column D and format the column with the appropriate number format.
Originally Posted by shg
As i said in my post, and showed in the image
the info is correct you are seeing the system date format in the formula bar, and in the cell whatever format you choose
Is there an issue now
try just adding a Date in another workbookso the date is correct
I suspect in the formula bar its showing your system date format, from the PC Settings - NOT EXCEL , as far as I know , thats the case
I'm in UK
If i change the format of the date cell to MM/DD/YYYY
then I get
12/08/2020 MM/DD/YYYY
BUT in the formula bar, i get my default system format 08/12/2020 = DD/MM/YYYY
enter a date
format to DD/MM/YYYY
Now look at the formula bar
its the way Excel works , NOT a PDF issue
Unless other issues
i formatted the column as MM/DD/YYYY
and the date seem all OK as i go through
In your attachment, the first date shows as 12/08/2020, but should show 8/12/2020. I have the original police reports and 8/12/2020 is correct.
It appears as I go through dates that have middle numbers of 12 or less, then the Formula bar shows them incorrectly. As example Incident#'s: 77984420
78001320, 62950220 show actual dates of 6/12/2020 and 5/12/2020 whereas the formula bar shows 12/06/2020, 12/06/2020 and 12/05/2020.
This becomes important when I format to a date to save space like 8/12/2020 to 8/12/20. When I do this, the date changes to 12/8/20.
I can leave it as is and do my work, but with hundreds of thousands of cell lines to do, I need to find why this is happening and correct it. Thanks
The problem is the leading zero on the day - it's causing the dates to be read in standard European format (DD/MM/YYYY ) instead of US (m/d/yyyy).
Can you attach the original PDF? How are you converting and importing it? Where does it originate? Europe or the US?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
What date your PC clock is displaying? (in the bottom right corner) dd/mm/yyyy or mm/dd/yyyy?
Quang PT
I will attach a portion of the PDF. I converted to excel using Adobe Acrobat X Pro
Here is a portion of the PDF.
Date is shown on PC as 1/12/2021
Dear Pick44,
If you look at the Pdf file, the month is on the left and the dates are in the center.
Incident- 19029219......Date - 02/12/2019 You might think it to be 2nd of December or 12th of february.
Have a look at the next line
Incident- 66495819......Date - 05/26/2019
Here the person who has sent the PDF file to you has put all dates in the center and months at the left.
Because months are only 12, but the date 26th is in the center so the format in the PDF for dates below are (mm/dd/yyyy).
Check all dates are in the same order in the PDF file. It has nothing to do with date format. You have to copy the dates from the PDF in this format (mm/dd/yyyy)
Regards,
Mukhtar56 (India)
I have used PowerQuery to import your PDF and to manipulate the dates column to ensure that they are read as real dates. Open it and see if you now get what you want. If not, let me know and I shall try to tweak it to work for you and explain how it's done.
AliGW
Yes, dates are correct. Is PowerQuery a download you use or a pay as you go thing? I would like to use it to convert future PDF files . Thanks
It is built into Excel 2016. Look at the Data Ribbon - Get & Transform - Get Data - From File - From PDF ...
Just navigate to your PDF and load it in. I used the Split function in PQ to split the date column into three, moved the columns into the right order, then brought the columns back together in the right order using Merge and then changed the column format to date.
Let us know how you get on.
Downloading now. Lots of pages , so it is taking time. But I opened one page and see the dates are correct. THANK YOU.....
Excel loading and not responding
One page of 144 pages loaded and then stopped. Since this is first time using, i am sure i missed up. Will try again
It saved each to a separate page. I have 144 tabs. Without copy and paste, is there an easy way to have them on one page?
You would need to load them to a query and then append them before loading them to the workbook. If you can provide a 3-page sample PDF to test, one of us can talk you through it.
okay thanks. Here are pdf files... 3 pages
Did not see them upload the first time. trying again.
I’m signing off now. If nobody else chips in before tomorrow morning, I’ll have a look then.
I saw it this time load
Great. I’ll look tomorrow morning at this.
Thank you, blessings,
In the Import dialog, select your tables and then Load to ... (Connection Only).
Open the Table001 query and on the Transform ribbon, select use Headers as Use First Row to demote the header row.
Get Data - Combine Queries - Append - Three or more - add tables from the list on the left to the list on the right (SHIFT select and click Add) - OK.
On the Transform ribbon, select Use First Row as Headers to re-promote the header row - close and load.
After I click Get Data & select my PDF file, I click Multiple Files and have either Load or Transform. I choose Load. And this is screen I get and it normally says Excel Not Responding or not enough memory.
Multiple files??? That’s not an option, so I’m not sure what’s happening. How many pages in the PDF? Can you attach the full document?
I will give you a screen shot. See attached. There are 71 pages of pdf
Did you select all 142 items or just the top 71?
71 pages. Should be approx 1200 rows of data on these 71 pages of pdf
Not sure what 142 items your referring too?
If you scroll down you should see the pages and then the whole lot repeated as tables. That’s why it says 142 items at the top! So, again - are you sure you are selecting JUST the first 71 items? It should not be too much for Excel to handle, but I will test it for you tomorrow if you are able to share the whole thing.
Unfortunately, cannot send file. Protocol issues with my work.
Thanks, blessings,
But you could answer the question!
Sorry - not much more I can do in the circumstances.
Sorry about not answering. Yes, I selected all the 71 files and then clicked Load. I also did same thing and hit Transform..... and both times it hung up and froze.
Thank you so much for all your efforts. Blessings, richard ><>:
AliGW... finally figurerd out how to do by following your instructions. Thanks. Here is a sample of the report. As you can see all worked out including dates being correct. The only issue I have left to figure out is the Address. The # and Street Name on "some" are together with no spacing. The Addresses with 100 XZY are fine. It is just whose with 5 numbers before the address. Again, thanks for your patience and help. ><>
I don’t see an issue with addresses in the sample - all have spaces.
Look at 12000Northborough. No space. All numbers with more than 3 digits do not have a space. Look at F5 thru F15.
Spaces show on my mobile device.
Try splitting the column in PQ (leaving settings as they come up by default), then re-merge using a space as the delimiter.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks