Forum Discussion

Austen_E's avatar
Austen_E
Copper Contributor
Nov 02, 2022

Excel TEXT function unexpected behaviour

I'm in a pickle with an Excel formula using the TEXT function in a legacy spreadsheet used in my organization. Unexpected Behaviour Formula: =TEXT("2/11/2022","yyyyMMdd") Expected Result: 20221102...
Formulas and Functions
Sekoleyte's avatar
Sekoleyte
Iron Contributor
Nov 02, 2022

Austen_E 

Hi there.

2.11.2022 in date format equals 44867 as a number. if you write 44867 instead of 2.11.2022 it will work.

Formula: =TEXT("44867","yyyyMMdd")

 

if you use cell reference instead of quotation mark, it always works no matter what the format is:

 

Formula: =TEXT("A1","yyyyMMdd")

Formula: =TEXT("B1","yyyyMMdd")

 

  • Austen_E's avatar
    Austen_E
    Copper Contributor
    Nov 02, 2022
    "if you use cell reference instead of quotation mark, it always works no matter what the format is:"

    Unfortunately this is not correct! I tested with both manual inputs and Cell inputs - e.g. see below:
    Input "=TEXT(Input, "yyyyMMdd)
    02/11/2022 02/11/2022
    02-11-2022 02-11-2022
    2022-11-02 20221102
    02-Nov-22 20221102
    • Sekoleyte's avatar
      Sekoleyte
      Iron Contributor
      Nov 02, 2022

      Can you please add a document that you have a problem?
      i tried to show you how it works. did you write the formula below in quotation mark?

      Formula: =TEXT("44867","yyyyMMdd")

      • Austen_E's avatar
        Austen_E
        Copper Contributor
        Nov 02, 2022

        Sekoleyte 

         

        (NB: This is not the same behaviour across computers! testing this on a colleague's computer resulted in every cell in the right column displaying "20221102" correctly - we have the same laptop model and Windows/Excel install versions)

Resources