I am looking for a formula to return the date when I input a week number. This is complicated by fact that I do not know which formula my calendar, BusyCal, uses to calculate the week number. This calendar shows this week, starting on Sunday, is week 14. For example, if I input week number 21 for the year 2022 I would like the spreadsheet to return something like 05-22-22 or May 22, 2022. I have looked for help but none of the formulas worked. Also I want the formula to pull from cells in the spreadsheet where I would input the year and week number so that if the week number or year changes the new date is changed automatically.
Hello,
Based upon WEEKNUM, and using your BusyCal example of week 14, the weeks run from Monday through Sunday. With that, placing the year in A1, the week number in A2, and this formula in A3:
=((A1 & "-1-1") + (7-(WEEKDAY(A1 & "-1-1",2)) + ((B1-1)*7)))
you get this:
Format column C to your needs.
Edit:
Note this all changes if BusyCal is based upon ISO 8601. Check if Jan 1 of this year is the first week of the year.
Edit 2:
I am re-posting this as a comment as there are too many open questions as to the determination of the week number from BusyCal. Further definition is needed as there is also a function ISOWEEKNUM which could explain the week number.
1 Like
Thank you Ratslinger. I now have a good starting point. I just need to reformat my spreadsheet with 2 additional columns. I also found that this works: â=WEEKNUM(DATE(YYYY;MM;DD);2)â.
The ISO date string â2022-1-1â can be used instead of the correct number returned by DATE(2022;1;1)
-
Whatâs âthis weekâ. Timeâs going on.
-
To get an answer to the question regarding the âBusyCalâ calendar isnât just complicated, but impossible for somebody not knowing how they (th editor ?) number the weeks - and if they use any specification at all for more than one given year.
-
To avoid that kind of stabbing in the dark there are internationally approved standards developed and published by ISO. ISO-weeks are always starting with a monday (number 1, sunday is day number 7), and for every year the weeknumber 1 is assigned to the week containing the first thursday of the year.
-
As you can see in detail when reading ISO week date - Wikipedia, there is a fuindamental incomatibility:
You need to either accept that in some cases one week gets different weeknumbers depending on whether you look at one day of it or on another.
Or you need to accept that uniquely numbered weeks contain days of two different years of our solar calendar. .
Anyway: Donât leave decisions of the discussed kind to propbably insufficiently informed or cwilful calendar printers. Stick to ISO. They know what they are doing. Weeknumbering is only of use if communication using the numbers is unambiguous.
Just for fun I made a (hopefully correct) demo:
disask76200IsoWeekNums.ods (34.7 KB)
1 Like
Vielen Dank Wolfgang.
Well I found a solution. Here is the formula:
=DATE((cell showing year),1,1) - WEEKDAY(DATE((cell showing year),1,1),1) + (cell showing week number)-1)*7 + 8.
I really donât understand exactly how it does itsâ calculations but it works. Also I changed the last item from 1 to 8. I have learned that not all systems count week numbers the same. Some always count the week with Jan 1 as the first week of the year. Others count it differently depending the day of the week on which Jan 1 falls.
One impressive feature of Libre Office is that in my spreadsheet I had my date format as in MMM DD format and it automatically used this format on subsequent cells.
Here is an example. The top row is the week numbers the bottom row is the abbreviated date for the first Sunday of the particular week number.
I highly doubt it works. It adds 8 to the found week, meaning it thinks that this yearâs (2022) January 1st was in the previous yearâs weeks (which is true for several possible week numbering systems, including ISO). And that means that it will most likely also work for 2023. But it will definitely fail for 2024, and you will not remember the details at that point, and may even overlook the error and have some problems because of that (depending on the importance of your calculations).
So this:
is the worst possible attitude to the calculations. Understanding what you calculate and how is the critical piece of using spreadsheets.
You need to perform a proper experimentation with your âBusyCalâ to learn which numbering system it uses, asking for week numbers for all dates from Dec 25th to Jan 7th for several years, and see where it puts the border. This would allow you to come with a proper formula.
Thank you mikekaganski. I understand your point. As I stated I donât understand exactly how this formula works. What I wanted was a way to make adjustments to a events on timeline without having to click over to look at the BusyCal app and then scroll through it to get the Sunday date of a particular week. I know that after I changed the 1 in the formula to an 8, essentially adding an extra week it, does match up with the week numbering used by BusyCal. I did write the developer but never heard back from him.
I will when my life slows down read more about the ISO formulas. In the meantime I will add a note somewhere about my edit to this formula. Your comments caused me to look at the build in Calendar app in Mac OS Monterrey and I realized that it uses another numbering system for weeks. The issue is that BusyCal has some features built in that the Mac OS calendar app does not have.
Everything depends on the preconditions. Which standards do you want to follow? Which is the first day of any week? Here in Germany Monday is the first day of week and Sunday is the seventh day. Week numbering follwows the ISO rule that week #1 is the week that includes January 4th. If I would make a calendar for Germany, the first days of each year 2000-2029 would be:
2000 2000-01-03 Mo =DATE(A1;1;4)-WEEKDAY(CURRENT();3) [Monday at or before Jan 4th]
2001 2001-01-01 Mo
2002 2001-12-31 Mo
2003 2002-12-30 Mo
2004 2003-12-29 Mo
2005 2005-01-03 Mo
2006 2006-01-02 Mo
2007 2007-01-01 Mo
2008 2007-12-31 Mo
2009 2008-12-29 Mo
2010 2010-01-04 Mo
2011 2011-01-03 Mo
2012 2012-01-02 Mo
2013 2012-12-31 Mo
2014 2013-12-30 Mo
2015 2014-12-29 Mo
2016 2016-01-04 Mo
2017 2017-01-02 Mo
2018 2018-01-01 Mo
2019 2018-12-31 Mo
2020 2019-12-30 Mo
2021 2021-01-04 Mo
2022 2022-01-03 Mo
2023 2023-01-02 Mo
2024 2024-01-01 Mo
2025 2024-12-30 Mo
2026 2025-12-29 Mo
2027 2027-01-04 Mo
2028 2028-01-03 Mo
2029 2029-01-01 Mo
Once you made this basic facts clear, everything becomes easy.
You are right Villeroy. It is interesting. I am aware that the Hebrew calendar has different years. I think that China and some other far eastern countries have lunar calendars and different years. I just like using formulas when I can. It can also be a good mental exercise to write a formula for a spreadsheet.
https://help.libreoffice.org/7.3/en-US/text/scalc/01/func_weekday.html?DbPAR=CALC#bm_id3154925
DATE(year;1;4) returns the 4th of January for a given year.
Because of type=3 my formula subtracts 0 if DATE(year;1;4) is a Monday, 1 in case of Tuesday, âŚ, 6 in case of Sunday, so the result is always the Monday at or before 4th of January.