In a monthly spreadsheet I have cells which contain the year (A1) Month (A2) and then the rest of the column running from cell A4 through to A34 numbered as the day in the Month (1 to 31).

My objective is to place the correct day of the week (in text) besides the day of the month (number).

So far I have successfully used the following formulae individually -

=(DATE(A1;A2;A5)) in this case returning 02/10/2020 =CHOOSE(A4;"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun") returning Mon (A4=1) =WEEKDAY(DATE(A1;A2;A5);2) which gives 5 indicating Friday (correct for 02/10/20) =DAY(DATE(A1;A2;A5))

WEEKDAY(date()) returns the number of the day and parameter 2 indicates that the week begins on Monday.

The date format required is the English version DD/MM/YY

What I now need to do is to somehow combine these formulae to copy down in cells B4 to B34 in order to place the correct day of the week against the day number in respect of that month but this so far eludes me.

A suggested working formula would be much appreciated.

The references I have used:


Interestingly, all these references suggest comma delimiters rather than semicolons between the formula elements.


There is no need to use a formula, this could be achieved by using cell format. Click Format > Number > More formats > Custom number formats then use dddd d

  • dddd for the day of the week.
  • d for the day of the month (1,2, etc. or dd for 01, 02, etc.)
| improve this answer | |
  • 1
    So, I still have to use the formula to construct the date in the cell as =(DATE($A$1;$A$2;$A5)) then formatting the cell as per your answer works a treat. (using only dddd in this case). Thanks very much! Another problem solved. – User24601 Oct 3 at 8:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.