Excel Weekday Function Issue

[PDT]

I have an issue (or lack of understanding) of the Weekday function (Excel O365)

Formula in Column B is =weekday(Col A,2) cell in column B formatted as general
Formula in Column C is =weekday(Col A,2) cell in Column C is formatted as ddd

Column A Column B Column C
30/10/2019 3 Tue

I don't understand why I get different results, column B is correct, column C is incorrect dependent upon how the cell is formatted.
As an aside if I remove the return type 2 from the Weekday function in column C then it returns the correct day (Wed)

Can anyone explain to me why this is happening?

 

[Joe4]

The cell format "ddd" returns the day of the DATE in the cell it is being applied to.

It is important to understand how Excel stores dates. It actually stores them as number, specifically, the number of days since 1/0/1900. To see this, go to any valid entered date, and change the format to General, and you will see the date as Excel does (all a date is in Excel is a number with a special format).

So, when you use the WEEKDAY function, you are returning a number (usually between 1 and 7). If you apply a date format to that, you are actually the date represented by that number (i.e. 4 in dates really means 1/4/1900).
So applying the "ddd" format to that is really applying it to the date 1/4/1900.

If you want the weekday name of the date, just use the "ddd" on the date straight-up, don't apply the weekday function to it.

 

[Fluff]

The weekday function is working correctly, it's the fact that Xl "sees" 1 as Sunday, 2 as Monday etc.
Therefore with the formula returning 3 Xl shows that as Tuesday

 

[sadath]

=weekday(Col A,2) returns the week number ie a numeric number 3 (not date its a numeric value)
then you try to format that number number to date, will return Tue (equivalent date of numeric value 3 is 3/Jan/1900, which is Tuesday)

I Think you need
Formula in Column C =(Col A) cell in Column C is formatted as ddd

 

[PDT]

Thanks for the quick response, it all makes sense when you explain it like that

 

[Fluff]

Glad we could help & thanks for the feedback