Query Editor - Issue with coverting date format when year changes when date format is Day DD MMM e.g Sat 28 Dec

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
click on Error and it will tell you what kind of error is
 

Kerryx

Board Regular
Joined
May 6, 2016
Messages
82
DataFormat.Error:We couldn't parse the input provided as Date value
Details:
Sat 11 Jan
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
I think your source table contain dates stored as text not as date

select in source Sat 11 Jan and look at formula bar, if you see the same that means this is a text not a date
 
Last edited:

Kerryx

Board Regular
Joined
May 6, 2016
Messages
82
Probably is as it is in format at
Sat 28 Dec
Wed 22 Jan
Sat 1 Feb

everything prior to Wed 1 Jan converts to date ok as I assume it looks and takes current system year for date but runs into trouble when it hits Jan 1 as It cannot determine next year is 2020
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
I suggest use normal format date: dd/mm/yyyy or in US style: mm/dd/yyyy without custom formatting like: Sat 1 Feb
 

Kerryx

Board Regular
Joined
May 6, 2016
Messages
82
Wish I could but data being pulled from a website so gotta work with what I got
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,730
data being pulled from a website
you need to convert data to be a proper data (date)
if on website dates are as dates it's ok, but if as text you should convert it manually

btw. Sat 11 Jan is not a date, simply this is a text that pretends to be a date
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,101,786
Messages
5,482,923
Members
407,368
Latest member
FunkyFriedChicken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top