Update automatically dates and Gray weekend days

[nirija]

Hello,

I have a statistics table with data by day and by person for the entire calendar year.

I would like to maintain this table from one year to the next and find formulas to make the yearly transition easy.

Here s attached the original table.
TPV-2025.ods (320.3 KB)

Each year, I modify the dates one by one, I gray out the columns corresponding to Saturdays and Sundays, and sometimes I shift my table to February in a leap year.

Is there a way and formulas to automatically update the dates, so that the Saturdays and Sundays columns are grayed out, without having to redo everything and shift everything from one year to the next?

Do I need to completely redesign this table, or are there possible conditional formulas in this case?

Another related issue is that the rows correspond to employees.

Given that there are staff movements throughout the year, including arrivals and departures, but we can’t simply delete a row, otherwise it impacts the TOTAL sheet. We don’t know the total number of employees from one month to the next (the number of rows is random). Is there a solution to this problem?

Thank you for your help.

[fpy]

X-post

Mise à jour automatque des dates et griser les samed dimanche

dans le même genre : Calendrier de type perpétuel pour le travail - #2 by fpy → JOURSEM mettre la somme en haut

[Villeroy]

EternalCalendar.ods (63.7 KB)

Another demo where all the calendar cells have formulas, so there is no way to enter additional information.
Anyway, the concept is simple and straight forward.

1. $Calendar.$A$1 gets a year number. Skip the resulting calendar below that cell.
2. The days sheet has a most simple formula =DATE(Calendar.$A$1;COLUMN();ROW()) in A1:L31.
3. The holidays sheet has a calculated easter date and a list of calculated holidays on the right side. A1:L31 show a holiday name or an error: =VLOOKUP($Days.A1;$P$1:$Q$14;2;0)
4. Back to the calendar sheet, the header row shows the first row of the day sheet, formatted as month names. A4:L34 all have the same formula, showing either a holiday name from the holiday sheet or (in case of error #N/A) a date from the day sheet: =IFNA($Holydays.A1;$Days.A1)
   Most cells in A4:L34 are formatted by the “Day” style, the trailing days of short months are formatted by the “Next” style (gray, showing a date of next month).
   The conditional format is the same for all cells in A4:L34 except for the 29th of February.

[koyotak]

not your

@nirija

attached the original table

though a separate demonstration for you with marked weekends and marked easter holidays:
000_LO-CALC_marked weekends_050529.ods (46.0 KB)