Forum Discussion

RJ Johnson's avatar
RJ Johnson
Copper Contributor
Mar 01, 2017

Function =text(cell,"ddd") fails to convert a date to a day

I am exporting data from a database.

Column B contains a date/time and looks like this: 01/01/2017 07:10:59. If you select column B, the column type is "text."

I use the "text to columns feature," set the column as fixed width, and select Date as the column type for the date column. This results in column B conatining 01/01/2017 and column C containing 07:10:59. If you select column B now, Excel claims that this is a Date.

In column A I want the Day of the Week to appear.

In cell A1 I type:  =text(B1,"ddd") and I get exactly that string of text in the cell, not the day of the week.

 

Now for the real kicker.

If I go to the bottom of column B (say B500) and type in a date such as 03/01/2017 and put the same formula in column A (A500) - I get what I want which is the Day of the Week.

Any ideas?

 

This used to work. I have Office Professional 2010, upgrading is not an option. 

 

 

excel
Formulas and Functions
  • Tony20z220's avatar
    Tony20z220
    Copper Contributor
    May 20, 2022

    RJ Johnson 

    An old thread, I know but I figured it out.  You need to adapt the formula to your language.  My Excel is french, so I need to use "jjj" for jour instead of "ddd" for day.  

     

    When looking at Format Cells-> Custom you will see which letter your language-specific version of Excel is using.  In french it uses aaaa-mm-jj instead of yyyy-mm-dd.

    • fmolinares's avatar
      fmolinares
      Copper Contributor
      Jul 20, 2023

      Hi Tony20z220 

       

      I'm using 'ddd' in my formula but the name of the days are coming in Spanish. My computer language is English though. Do you know how can I make them come in English? 

       

      Thanks!

      • Tony20z220's avatar
        Tony20z220
        Copper Contributor
        Jul 20, 2023

        fmolinares 

         

        If the formula works, but the result is in the wrong language, I would start checking language settings in Excel or Windows.  They may be set to english but have a different region so excel is using the region to decide the language for the formula.  That would be my guess.


  • gnsgp's avatar
    gnsgp
    Copper Contributor
    Sep 18, 2019

    The problem is probably a conflict between the general system date settings and the excel date settings. I don't know how to change the excel date settings (and changing the date in the cells doesn't result in anything). However if you change the general system date settings (in my case Windows 10) to a different country format (in my case UK) then suddenly the formula works and you can say the name of the day instead of the "ddd". 

     

    RJ Johnson 

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Mar 01, 2017

    Hi RJ

     

    If your date is in cell B1   then try this   =TEXT(DATEVALUE(B1),"ddd")

    • RJGonzalez40's avatar
      RJGonzalez40
      Copper Contributor
      Feb 19, 2019
      Si am having an issue that sims to be in this ball park if it is not please direct to where i should go.

      I have been working on a file where i take weight x ft x 3 = formula on export i get the formula expression instead of the actual formula result.

      How can i export as the result instead of the formula
      • Wyn Hopkins's avatar
        Wyn Hopkins
        MVP
        Feb 19, 2019
        Hi, could you start a new post with your question

        More chance of getting a response and it’s best to avoid mixing questions

        Thanks
    • RJ Johnson's avatar
      RJ Johnson
      Copper Contributor
      Mar 02, 2017

      Thank you, Wyn but this does not work either.

      I feel like there is some sort of hidden formatting or switch that has been toggled when the file is exported from the database. Like I said, sometimes my first attempt and your suggestion do work.

Resources