Quick Tip: Google Sheets and Calendar Weeks

When outputting calendar weeks on a specific date, you have to be careful in Google Sheets (just like in Microsoft Excel). The function = CALENDAR WEEK (TODAY ()) calculates using the American standard, for which the first day of the year is always assigned to calendar week 1. In Germany this is defined differently: Here the first calendar week is the week from which at least 4 days fall into the new year. In Google Sheets you can solve the problem (in addition to other, exciting alternatives like = SHORTEN ((TODAY () - DATE (YEAR (TODAY () - REST (TODAY () - 2; 7) +3); 1; REST (TODAY ( ) -2; 7) -9)) / 7) and the modified = CALENDAR WEEK (TODAY (); 21) ) easily with the specially designed function = ISOWEEKNUM (TODAY ()) .


Back