Forum Discussion

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," ...

excel

Formulas and Functions

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
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
  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.
  - SergeiBaklan
    Diamond Contributor
    Jul 20, 2023
    Tony20z220
    
    Excel uses regional settings to recognize dates and current language to perform operations on it by formula.