How to Convert Number to Date

[Chanleakna123]

hi all , 

i have around 300 Excel File to be imported as Folder in Power BI , 

In each excel those are in date Format , when i import into Power BI , it turns out to Number instead , and i tryna change to Date Format , but it turns out still error. 

the same error message appear. 

may i know how to settle this ? 
i don't expect to change all of the Date to other format . becuase i have 300 Excel file which are the same format . 

In power BI , showing Number

[amitchandak]

@Chanleakna123 , One of the two should work

Delivery Date New = date(1970,01,01) +[Delivery Date]

or

Delivery Date New = date(1900,01,01) +[Delivery Date]

[PhilipTreacy]

Hi @Chanleakna123 

44100 is the Excel serial number for the date Sep 26 2020. In your worksheet the date is entered as a date in m/d/y format.

If you are also using m/d/y format it should automatically be recognised as a date.  But you can see from the top of the Date columns that they are set as any (abc 123) type.

PQ has probably done an automatic type conversion as the 2nd step after importing, perhaps because you are not using d/m/y format.  Delete this and set the date properly using Change Type -> Using Locale or just by clicking on the abc 123 and then clicking Date.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Chanleakna123]

Hi , @amitchandak  @PhilipTreacy , my

file imported in BI , appeared to hve some row Sync  as date and some sync as serial number. 
omce I convert to date , the serial might be error. 

[Anonymous]

Hi @Chanleakna123,

Perhaps you can try to add a new column on the 'query editor' side to store date value who transforms from the serial number, then you can use both of two type of values at the same time.

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.AddDays(Date.FromText("1/1/1960"),Number.From ([Delivery Date] )))

Regards,

Xiaoxin Sheng