Forum Discussion
TEXT function to format a date
Hi Peter,
NOTE: the dates in Excel are actually sequential serial numbers, please see https://support.microsoft.com/en-us/help/214094/how-to-use-dates-and-times-in-excel 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!
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?
