Forum Discussion

Yugan475's avatar
Yugan475
Copper Contributor
Jul 18, 2020

Date Format

The present Date Format is 

2020-06-17T00:00:00.000Z

 

I want it as " YYYY-MM-DD" 

Please suggest

excel

3 Replies

  • OwenPrice's avatar
    OwenPrice
    Iron Contributor
    Jul 18, 2020

    Yugan475 

     

    Suppose your date is in cell A1, use this:

     

     

    =LEFT(A1,10)

     

     

    As you can see, this simply takes the first 10 characters from your current timestamp string and Excel properly recognizes it as a date, on which you can use Date-based functions and date arithmetic:

     

     

    EDIT:

     

    Some useful feedback from SergeiBaklan regarding the above recommendation.

    While Excel acting on the above formula and arithmetic will recognize the result as a date, it is not foolproof, so wrap the LEFT function with DATEVALUE to force the result into the correct underlying format. Then format the cell as a date.

     

     

    =DATEVALUE(LEFT(A1,10))

     


    I prefer DATEVALUE to the -- operator as what's happening is more clear to readers.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 18, 2020

      OwenPrice 

      This formula returns text, not date. If, for example, check

      =B1>DATE(2021,1,1)

      it returns TRUE since any text is always "more" than any number. Another story is that use such text in arithmetic operations or some formulas Excel automatically transforms text to number. Same as ="10"+1 returns 11.

      More reliable will be to push such conversion as =--LEFT(A1,10) and apply date format to the result.

       

      If we have column with datetimezone texts we may apply to it Date->Text to Columns using T as separator and remove second column after that.

Resources