TEXT function to format a date

[Peter Coles]

Hi! I’m Pete!

I have a query regarding Excel 2010 and Excel 2013 that I hope you can answer.

It’s regarding the TEXT function, and using it to format a date:

My experiment is thus:

TODAY()	01/02/2018
TEXT(TODAY(),"dd/mm/yyyy")	01/02/2018
DAY(TODAY())	1
TEXT(DAY(TODAY()),"dd")	01
MONTH(TODAY())	2
TEXT(MONTH(TODAY()),"mm")	01
YEAR(TODAY())	2018
TEXT(YEAR(TODAY()),"yyyy")	1905

Could you please be kind enough to explain why Excel is using 1 Jan 1905 for TEXT function?

[#2]

Hi Peter,

NOTE: the dates in Excel are actually sequential serial numbers, please see this support article to learn more about that

You've extracted the year 2018 from TODAY function, and 2018 is the serial number that represents the date 7/10/1905 (M/d/yyyy).

Just try to put 2018 in any cell and format the cell to short date and see that.

When you put the 2018 serial number in TEXT function with the format of yyyy, you've got the year of the date 7/10/1905! 

That's it

TEXT(YEAR(TODAY()),"yyyy") became in the evaluation mode as below:

TEXT(2018,"yyyy") 

As we previously knew that 2018 represents the date 7/10/1905 so 

TEXT(2018,"yyyy")

Equal to

TEXT("7/10/1905","yyyy")

Anyway, you don't need the last formula, the formula that came before is enough!

[#3]

@Haytham Amairah Hello! I am by no means an excel or PowerBI expert. Long story short. I am importing an excel file into PowerBI for visuals on a monthly report. I need to display the data by our institution's Fiscal Year (Sept - Aug) for it to be meaningful. The data goes all the way back to 2017. I found a formula =IF(MONTH(C4486)<9,YEAR(C4486),YEAR(C4486)+1). In number format, it works but in date format, they all say 1905. I need it to be recognized as a date in PowerBI so that it will use the date hierarchy function. What is the best way to do this?