Calendar functions in excel RRS feed

  • Question

  • I have a spreadsheet, that i am using to manage the rota for my team, so one sheet per month. Using the formula as the heading to the table to auto calculate the month / day of the year =TEXT(WEEKDAY(DATE(CalendarYear,1,1),1),"aaa") I get the heading displayed as Sun, Mon etc depending on the year. As Below. Employee Name Sun Mon Tues Wed 1 2 3 4 5 Is there a way to convert the text to also show the day in numeric as well?

    EG: 01 Sun 02 Mon etc

    Thank you,


    Wednesday, July 26, 2017 12:30 PM

All replies

  • Hi

    Would you accept a macro, just a push of a button or from the macro menu and your layout would look like the screen shot below.


    Wednesday, July 26, 2017 4:45 PM
  • Hi,
    Sorry, I can hardly understand your intention clearly. If I understand it, I could make a sample macro.
    Would you provide a sample image that is filled with five members and one week (from Sun to Sat)?


    Wednesday, July 26, 2017 10:28 PM
  • Hi Ahri1978,

    The Weekday function returns a number and then the Text function change it into Sun, Mon format.

    So I think you just need to remove the Text function.

    Such as  =WEEKDAY(DATE(2017;7;27);1)

    You could see below link for more information.

    Best Regards,


    Thursday, July 27, 2017 7:16 AM
  • Hi Ahri1978,

    Has your original issue been resolved? If it has, I would suggest you mark the helpful reply or provide your solution and then mark it as answer to close this thread. 
    If not, please feel free to let us know your current issue.

    Best Regards,


    Thursday, August 3, 2017 8:25 AM