Automatize WEEKDAY function

[matitaccia]

Dear All,

I currently dispose of a working sheet which I use to keep track of my working hours. We recently have upgraded our Microsoft Office 2010 to Microsoft Office 365 and this particular sheet has stopped working correctly: it shows a lot of #value! errors.

I have found how to correct the the #value! errors, but I think that there should be a better way to solve this.

The function which is creating mistakes is the following one:

=WEEKDAY(D11 & "/" & $E$5 & "/" & Anno,2)

were D11 is a cell containing the number of the day in the month (1 --> 31), while E5 refers to the month and "Anno" refers to current year. In order to solve the issue I tried to use the WEEKDAY syntax as explained on the Microsoft Support page, i.e. by translating 01.01.2017 in the number 42736. If this transformation is performed, the file works again. This would mean though, that with every new year the number should be added manually: this wasn't happening before.

By observing the syntax used originally it seems to me that, by giving Excel the day number, month and the year Excel was able to automatically understand the correct weekday: I would like to have it that way again.

Anyone has suggestions on how to modify the syntax used?

Thank you very much,

matitaccia

 

[matitaccia]

I forgot to publish a screen shot, which may help to understand a little bit more: link