Issue:
Transaction activity exports from paypal have dates in m/d/yyyy format. Opening in Excel set to NORMAL date formats (d/m/y etc) for the rest of the entire goddam planet format results in 2 issues which screw up importing into my excel cashbook (used for GST returns)
- Dates recognised as dates are incorrect with m & d switched
- so we see ‘4/01/2018’ for a source date of 1 Apr 2018 but it is actually the date value for 4 Jan 2018
- Dates not recognised are just text
- for 31 Mar 2018 we get text ‘3/31/2018
Need a formula to convert to correct excel date values that works across both errors above, and here is my version (assuming source date is in cell A4):
=IF(ISNUMBER(A4),DATE(YEAR(A4),DAY(A4),MONTH(A4)),DATEVALUE(MID(A4,FIND("/",A4,1)+1,FIND("/",A4,FIND("/",A4,1)+1)-FIND("/",A4,1)) & LEFT(A4,FIND("/",A4,1)-1) & RIGHT(A4,5)))