More French Budget Complexities

It’s always interesting to see how people do things in other places. Our school in France has a lot of non-standard things going on. This one is based on the idea that overtime and recuperation time (coming in late to make up for working longer) needs to balance out for each week that starts with a Monday in the month. So if the Monday falls on the 4th or the 31st, that week stays with that month.

Find all the Mondays

First up, get all the Mondays for the months in the year and write them to a sheet called “reference.” Thanks to humans at Stackoverflow I had an easy answer. It’s also worth noting that even if I got that answer from AI it’d be because people wrote it in Stackoverflow and other places and then other people came up with the AI to make that information findable.

//from https://stackoverflow.com/questions/9481158/how-can-i-get-the-4-mondays-of-a-month-with-js

function getMonthMondays(date) {
    var d = new Date(date),
        month = d.getMonth(),
        mondays = [];
        sundays = [];

    d.setDate(1);

    // Get the first Monday in the month
    while (d.getDay() !== 1) {
        d.setDate(d.getDate() + 1);
    }

    // Get all the other Mondays in the month
    while (d.getMonth() === month) {
       const newDate = new Date(d.getTime());
       const simpleDate = newDate.getFullYear()+'/'+(newDate.getMonth()+1)+'/'+newDate.getDate(); 
        mondays.push(simpleDate);
        d.setDate(d.getDate() + 7);
    }
    Logger.log(mondays)
    return mondays;
}


function runYear(){
  const dates = [
    '09/01/2024',
    '10/01/2024',
    '11/01/2024',
    '12/01/2024',
    '01/01/2025',
    '02/01/2025',
    '03/01/2025',
    '04/01/2025',
    '05/01/2025',
    '06/01/2025',
    '07/01/2025',
    '08/01/2025',
  ]
  dates.forEach((date)=>{
      Logger.log(new Date(date).getMonth()+1)
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('reference');
      const mondayArrays = getMonthMondays(date);
      const range = sheet.appendRow(mondayArrays);
  })
}

The reference data

Now I’ve got a table of data with the first column being the month number and the subsequent columns being the date of any Monday falling on that month. There are at least 4 Mondays in each month and no more than 5 Mondays.

Monthly specifics

We’ve got people entering events on a main sheet named “data.” They enter the hours, put it on a date, and indicate if it’s overtime or recuperation time.

Now we have a sheet for each month for the summary data.

That’s for September and will use TRANSPOSE and QUERY to write all the Mondays vertically giving us the start date from the “reference” sheet we generated with the Google Script code.

Now in the next column, we’ll add six days to the Monday to get the end date.

So with the start and end date, we can use QUERY again to get the data specific to overtime and specific to each week.

Getting recuperation time is the same thing but with one change.

I did find one thing I think is a bug in Google Sheets. When I set the document to the France locale in document settings, the QUERY function fails entirely. I tried many permutations. None worked. It took me quite a while to figure out that locale was the issue. Interestingly, if the sheet is created when in the France locale it will fail even when the locale is set back to the USA. If you make a new sheet, the same QUERY will work fine but that sheet will always fail. You’re also ok to switch the document locale after creating all the queries. Finding problems like that is not pleasant. I feel like Google should give me some API credits or something.