Forum Discussion

Peter Coles's avatar
Peter Coles
Copper Contributor
Feb 01, 2018

TEXT function to format a date

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?

Excel Online
Formulas & Functions

2 Replies

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Feb 01, 2018

    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!

    • crjackson's avatar
      crjackson
      Copper Contributor
      Mar 04, 2022

      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?

Resources