Hi guys I need a hand, in the file that I attach I have a date column which is in text format. When I import the file into a query connection table it displays the correct data but if I change the column to date format power qyery gives me an error how can i fix it? i tried to create a custom column,but sometimes it works sometimes not.
thank you allOFReport2 (ETD)_FCO_20230608_20230608.xls
The file is in .xls format, so the query has been stripped out. Save in .xlsx format.
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.
Hi thanks for the help, I have not attached the file with the query, but only the file that is imported to show you what format the date was in
example 80623 but I would like it 08/06/2023
select column B and set to short date format then save to xlsx and use in Power Query
Hi I tried to do as you say but it gives me this value 09/25/2120
Anyway the file I have attached is located in a folder together with the master file. When I open the Master file it automatically loads the data
what format is of your system date?
Hi, sorry if you are talking about the master file it is in xlsb office 365
I am asking about your system date
I can't help with the power query stuff, but, if it helps, I note that the serial number 80623 (the number entered in column B) is the serial number for 25 September 2120. It looks like your current attempts with Power Query are simply copying the number in column B as if it is the serial number for the desired date. You have somehow not instructed PQ how it should parse the number/text to get month, year, and day from the value entered.
Originally Posted by shg
this date format is correct for you?
Date
Hi yes exactly it just should be 80623= 08/06/2023 day,mounth,year
Last edited by francescoslash77; 06-09-2023 at 04:54 PM.
Re: convert text to date in power query
so here is,
load to the sheet or stay with query only, it's up to you
btw. there is no text in B column so enough to change serial number to date
Thank you for your help, but I can't change the date in the attached file, because it's a file that changes every day. I download it from a company program and insert it in the folder where I have the master file. then the date is not 09/25/2120
but it must come out 08/06/2023 the date yesterday's date
so your description in the first post is not enough clear
use From Folder option and change column [Date] to date format
what is difference between yesterday's date and week ago date? date is date nothing more!
and describe detailed here what are you really doing - step by step
edit: and I asked you about your system date! could you answer ?
and update your profile about excel Product Number and Version
Last edited by sandy666; 06-09-2023 at 05:16 PM.
if I do it from power query it gives me an errorerror.PNGerror1.PNG
Maybe as Mr Shorty said I need to change something in power query setup
add column with Number.From([Date]) then change type to Date
and could you answer on my question?
Sorry but I don't understand what you mean by system date
do you have SYSTEM MacOS, Windows, Unix, Linux? choose one and tell me about system date
I am using Windows 10 EnterpriseAttachment 832473
look at this
datetime.png
your attachment is added incorrectly
ok, use function from post#18 to get serial number from text and then change to date
I think I solved it, can I delete the old date column?Column.png
yes, you can then move your new column into proper position
Thank you, you were very kind
you are welcome
if the problem is solved, make me happy and hit Add Reputation (bottom left corner next to the post that was helpful)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks