I would like to set up a functionality for Google Sheets that provides a start and end day for a given week index number of the year. When using the Utilities.parseDate and Utilities.formatDate functions together with the 'w' and 'u' date format letters I ran into some unexpected behaviour where the 7th day of an indexed week actually precedes the 1st one.
Using Google Apps Script I'm trying to determine the first and last day of a week by week number. Like this:
const firstWeekday = Utilities.formatDate(Utilities.parseDate("2023-6-1", "GMT", "yyyy-w-u"), "GMT", "MMMM dd.");
const lastWeekday = Utilities.formatDate(Utilities.parseDate("2023-6-7", "GMT", "yyyy-w-u"), "GMT", "MMMM dd.");
This gives: February 6 as firstWeekday and February 5 as lastWeekday. Some further poking around showed me that the numberings are clearly misaligned between the two formats. Printing out the days of February 2023 in such a week number - weekday number format gives:
1: 05-2
2: 05-3
3: 05-4
4: 05-5
5: 05-6
6: 06-7
7: 06-1
8: 06-2
9: 06-3
10: 06-4
11: 06-5
12: 06-6
13: 07-7
14: 07-1
15: 07-2
16: 07-3
17: 07-4
18: 07-5
19: 07-6
20: 08-7
21: 08-1
22: 08-2
23: 08-3
24: 08-4
25: 08-5
26: 08-6
27: 09-7
28: 09-1
Based on this I assume the two formats count weeks differently, e.g. the 7th week of year 2023 starts from 13 February for the week number 'w' format, but from 14 February for the weekday number 'u' format. Is this right? If yes, how to use the two formats in sync? There is an obvious workaround of counting weeks from the 7th weekday to the 6th instead of 1-7. But if my understanding is correct, what is the proper way of handling this?