Excel: Count cells that contain weekday

Changing the format does not change the value. Your dates are still numbers to Excel, just formatted to show a string. As such Excel does not find a string.

COUNTIFS() will not work without a helper column. You need to use SUMPRODUCT():

=SUMPRODUCT(--(WEEKDAY(U2:INDEX(U:U,MATCH(1E+99,U:U)))=2))

SUMPRODUCT is an array type formula. Therefore one should not use full column references.

The U2:INDEX(U:U,MATCH(1E+99,U:U)) sets the reference range to only those with numbers. This way it is not doing unnecessary calculations.

WEEKDAY() returns the day number 1 = Sunday, 2 = Monday, ...

The SUMPRODUCT() will count when the criteria = TRUE. The -- Changes TRUE to 1 and FALSE to 0.