Date Wrong from PDF to Excedl

[redfishman]

When converting PDF to Excel, the dates show up in the formula bar as dd/mm/yyyy but show in the cell ( which is corrrect) as mm/dd/yyyy. This only happens if the middle number is less than 12. As example 02/12/2020 will show in formula bar as 12/2/2020. And if I format as mm/dd/yy it gives wrong date as 12/2/2020. How do I fix that?

 

[Akuini]

1. What is the date format in PDF? dd/mm/yyyy?
2. What is the date format on your local setting? dd/mm/yyyy?
3. Could you give more example, showing both the wrong & correct result?

 

[redfishman]

See PDF and the Conversion to Excel using PDF. My Adobe Acrobat X Pro is what I have for my PDF. See as example Incidents 17668816 & 60888816. Both of these have middle numbers of 12 or less. Click on the date of one of these and look at the formula bar. It has transposed the middle number to the front. Then format one of these to shorten the YYYY to YY and it shows as "Armenian", not US

TEST TEST pdf cho report.xlsx
	A	B	C	D	E	F
1	Incident#	Code	Short Description	Date	Time	Address
2	110211819	14	Destruction damage vandalism	08/24/2019	21:00	12803 NORTHBOROUGH DR
3	120272419	14	Destruction damage vandalism	09/14/2019	10:00	12803 NORTHBOROUGH DR
4	156961519	14	Destruction damage vandalism	11/28/2019	20:36	12803 NORTHBOROUGH DR
5	171260719	0	Murder non-negligent	12/29/2019	0:00	12803 NORTHBOROUGH DR
6	171336919	3	Robbery	12/29/2019	6:00	12803 NORTHBOROUGH DR
7	159277519	4	Aggravated Assault	12/03/2019	21:00	12803 NORTHBOROUGH DR
8	168877919	4	Aggravated Assault	12/23/2019	16:00	12803 NORTHBOROUGH DR
9	17668816	14	Criminal Mischief (Auto) > $1500 Loss	02/09/2016	6:00	12803 NORTHBOROUGH DR
10	53015716	14	Criminal Mischief (Auto) < $1500 Loss	04/26/2016	13:00	12803 NORTHBOROUGH DR
11	58964016	14	Criminal Mischief (Habitation) < $1500	05/08/2016	22:00	12803 NORTHBOROUGH DR
12	60888816	14	Criminal Mischief (Habitation) < $1500	05/12/2016	20:00	12803 NORTHBOROUGH DR
13	60583016	14	Criminal Mischief (Unclassified) < $1500	05/12/2016	8:00	12803 NORTHBOROUGH DR
14	67388616	14	Criminal Mischief (Auto) < $1500 Loss	05/26/2016	13:00	12803 NORTHBOROUGH DR
15	76510216	14	Criminal Mischief (Habitation) < $1500	06/14/2016	16:00	12803 NORTHBOROUGH DR
16	8054916	6	Theft - From Building or Habitation	01/19/2016	18:00	12803 NORTHBOROUGH DR
17	83069016	14	Criminal Mischief (Habitation) < $1500	06/28/2016	11:00	12803 NORTHBOROUGH DR
18	17048616	6	Theft - All Other Items (Miscellaneous)	02/08/2016	17:00	12803 NORTHBOROUGH DR
19	83168216	14	Criminal Mischief (Habitation) < $1500	06/28/2016	15:00	12803 NORTHBOROUGH DR
20	21144416	6	Theft - All Other Items (Miscellaneous)	02/17/2016	17:00	12803 NORTHBOROUGH DR
21	92969416	14	Criminal Mischief (Habitation) < $1500	07/19/2016	7:00	12803 NORTHBOROUGH DR
22	35951016	6	Burglary Motor Vehicle (Contents/Not-Part)	03/20/2016	22:00	12803 NORTHBOROUGH DR
23	100791416	14	Criminal Mischief (Auto) < $1500 Loss	08/02/2016	19:00	12803 NORTHBOROUGH DR
24	106105116	14	Criminal Mischief (Auto) < $1500 Loss	08/18/2016	23:00	12803 NORTHBOROUGH DR
25	45671016	6	Burglary Motor Vehicle (Contents/Not-Part)	04/09/2016	13:00	12803 NORTHBOROUGH DR
26	110099916	14	Criminal Mischief (Auto) < $1500 Loss	08/27/2016	22:00	12803 NORTHBOROUGH DR
27	111328216	14	Criminal Mischief (Habitation) > $1500	08/30/2016	18:00	12803 NORTHBOROUGH DR
28	111438516	14	Criminal Mischief (Auto) < $1500 Loss	08/31/2016	0:00	12803 NORTHBOROUGH DR
29	64886216	6	Burglary Motor Vehicle (Contents/Not-Part)	05/21/2016	9:00	12803 NORTHBOROUGH DR
30	113869716	14	Criminal Mischief (Habitation) < $1500	09/05/2016	12:00	12803 NORTHBOROUGH DR
31	153679817	8	Simple Assault	12/07/2017	0:00	12803 NORTHBOROUGH DR
32	80236716	6	Theft - All Other Items (Miscellaneous)	06/21/2016	13:00	12803 NORTHBOROUGH DR
33	150830516	14	Criminal Mischief (Auto) < $1500 Loss	11/26/2016	12:00	12803 NORTHBOROUGH DR
34	153630817	8	Simple Assault	12/07/2017	0:00	12803 NORTHBOROUGH DR
35	160972416	14	Criminal Mischief (Habitation) < $1500	12/17/2016	20:00	12803 NORTHBOROUGH DR
36	111753616	6	Theft - u.S. Mail	08/24/2016	17:00	12803 NORTHBOROUGH DR
37	160444216	14	Criminal Mischief (Habitation) < $1500	12/18/2016	18:00	12803 NORTHBOROUGH DR
38	109618716	6	Theft - All Other Items (Miscellaneous)	08/25/2016	19:00	12803 NORTHBOROUGH DR
39	160787816	14	Criminal Mischief (Habitation) < $1500	12/19/2016	13:00	12803 NORTHBOROUGH DR
40	161595316	14	Criminal Mischief (Habitation) > $1500	12/20/2016	9:00	12803 NORTHBOROUGH
41	138147716	6	Burglary Motor Vehicle (Contents/Not-Part)	10/19/2016	14:00	12803 NORTHBOROUGH DR
42	63815317	5	Burglary Habitation	05/21/2017	16:00	12803 NORTHBOROUGH DR
43	8166018	14	Vandalism	01/20/2018	5:00	12803 NORTHBOROUGH DR
44	138033916	6	Burglary Motor Vehicle (Contents/Not-Part)	10/29/2016	8:00	12803 NORTHBOROUGH DR
45	64214217	8	Assault - Class A/C by Bodily Force	05/22/2017	18:00	12803 NORTHBOROUGH DR
46	152425517	8	Simple Assault	12/04/2017	0:00	12803 NORTHBOROUGH DR
Table 1

 

[Akuini]

It looks like the date in PDF is dd-mm-yyyy but your local settings is mm-dd-yyyy.
See if this works:
If the middle number is < 13, then it will swap the day & month.

Sub flipDate1a()
Dim x As String
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("D2", Cells(Rows.Count, "D").End(xlUp))
    x = r.Text
    If CLng(Mid(x, 4, 2)) < 13 Then r.Value = DateSerial(Right(x, 4), Left(x, 2), Mid(x, 4, 2))
Next
Application.ScreenUpdating = True

End Sub

 

[redfishman]

It looks like the date in PDF is dd-mm-yyyy but your local settings is mm-dd-yyyy.
See if this works:
If the middle number is < 13, then it will swap the day & month.

Sub flipDate1a()
Dim x As String
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("D2", Cells(Rows.Count, "D").End(xlUp))
    x = r.Text
    If CLng(Mid(x, 4, 2)) < 13 Then r.Value = DateSerial(Right(x, 4), Left(x, 2), Mid(x, 4, 2))
Next
Application.ScreenUpdating = True

End Sub

Thank you.

 

[Akuini]

You're welcome.