incorrect day of the week

[Rhon Alvarez]

Hi Team,

Any idea on why excel is not recognizing the correct day of the week using the formula "text(day(cell),"ddd"). it is delayed by 1 day.

 

[bosco_yip]

1] The evaluation of your formula in B3 :

=TEXT(DAY(A3),"ddd")

>>

=TEXT(DAY(Monday, October 01, 2018),"ddd")

The DAY function return integer from 1 to 31 (representing the day of the month)

become >>

=TEXT(1,"ddd")

>>

Excel uses the 1900 date system, which means the first date is January 1, 1900

and become >>

=TEXT(January 01, 1900,"ddd")

Finally the formula return >>

=SUN

p.s. However, the result "Sun" is related to 1/1/1900 and don't related to 10/1/2018

2] The correct formula in your example is,

in B2 copied down :

=TEXT(A2,"ddd")

Regards
Bosco

 

[Hui]

or even simpler

=A2 and apply a Custom Number Format (Ctrl+1) of ddd

 

[Rhon Alvarez]

1] The evaluation of your formula in B3 :

=TEXT(DAY(A3),"ddd")

>>

=TEXT(DAY(Monday, October 01, 2018),"ddd")

The DAY function return integer from 1 to 31 (respenting the day of the month)

become >>

=TEXT(1,"ddd")

>>

Excel uses the 1900 date system, which means the first date is January 1, 1900

become >>

=TEXT(January 01, 1900,"ddd")

The formula result return >>

=SUN

p.s. However the result "Sun" is related to 1/1/1900 and don't related to 10/1/2018

2] The correct formula in your example is,

in B2 copied down :

=TEXT(A2,"ddd")

Regards
Bosco

Hi Hui,

Got it! thanks for the enlightenment.