By the way, who told you that there were 53 Thursdays in 2016? There were 52 
In the example given, I simply picked random numbers as the result. Nothing in the post (except for year numbers) was intended to be factual or true. My interest is in HOW to do it.
Well, try NETWORKDAYS function:
=NETWORKDAYS(DATE(B38;1;1);DATE(B38;12;31);;{1;1;1;1;0;1;1})
53 Thursdays were in 2020. The next time it will be in 2026… The rest of the time there are 52 of them as always.
2 Likes
| Date | Weeknum |
|---|---|
| 2015-12-30 Wed | 1 |
| 2015-12-31 Thu | 1 |
| 2016-01-01 Fri | 1 |
| 2016-01-02 Sat | 1 |
| 2016-01-03 Sun | 2 |
| 2016-01-04 Mon | 2 |
| 2016-01-05 Tue | 2 |
| 2016-12-27 Tue | 53 |
| 2016-12-28 Wed | 53 |
| 2016-12-29 Thu | 53 |
| 2016-12-30 Fri | 53 |
| 2016-12-31 Sat | 53 |
@Villeroy I’m sorry, what is this? The Thursday of 2015-12-31 was not in 2016; neither by ISO week count, nor in calendar year.
=WEEKNUM(DATE(2016;12;27);1) calculates the week number in US fashion. First week is the week which includes 1st of January and any week starts with Sunday. This way, the first week started as early as Mon, 2015-12-28 and the last week (the 53rd) ended on Saturday, 2016-12-31.
Weeknum_2016.ods (12.2 KB)
So you claim that the phrase “2015-12-31 was the first Thursday in 2016” makes sense? Note that it is orthogonal to week numbering.
The first Thursday in 2016 was 2016-01-07. 2015-12-31 was the Thursday in the first week of 2016 like 2016-01-03 was the Sunday of the last week 2015 when you count it the ISO way.
Agree. But why did you put it all here, when the claim “there was 53 Thursdays in 2016” is false in any case, regardless of the week numbering? It all looks as if the goal is to confuse a possible reader.
To answer your first question on this topic, I suspect that Calc’s WEEKNUM function or some US calendar told him that there were 53 Thursdays in 2016. All I wanted to do is to demonstrate how someone comes to the conclustion that 53 would be the correct result for 2016.
Ah! Thank you! And likely, I need to invent an excuse, like “I need some sleep”, to explain my stupidity 
Some reading for the night:
So all we discuss relies on the current calender…
The WEEKSINYEAR() function seems to see weeks as starting on Monday, regardless of locale, so it should be safe to use the previously given advice.
However, that function is not portable to Excel. If you need Excel compatibility for your worksheet, there is a cheat:
- In normal years there are 1 day more than 52 full weeks. To have more than 52 Thursdays, the year needs to begin and end on a Thursday.
- In leap years there are two days more than 52 full weeks. To have more than 52 Thursdays, the year needs to begin or end on a Thursday.
So, regardless of whether it is a leap year or not, if the year begins or ends on a Thursday, there are 53 Thursdays.
Assuming that A1 holds the year, this formula should also give what you are looking for.
=IF(OR(WEEKDAY(DATE(A1;1;1);1)=5;WEEKDAY(DATE(A1;12;31);1)=5);53;52)
Using the “mode 1” of WEEKDAY() to ensure maximum Excel compatibility.
An added advantage of this approach is that you can use it for any weekday, while WEEKSINYEAR only corresponds to weekday count for that one day in the middle of the week.
It is of course more convoluted, so if you only ever are going to count Thursdays, and you don’t need compatibility with other spreadsheet apps, the suggestion from @mikekaganski is certainly the more manageable one.
2 Likes
It is possible without this function: 
=IF(OR(MOD(DATE(A1;1;1);7)=5;MOD(DATE(A1;12;31);7)=5);53;52)
1 Like
WEEKDAY() would return numbers from 1 through 7 in both modes.
The MOD() function applied to integers with the module 7 returns a value in the range from 0 through 6.
Users will expect the result either complying with “Sun=1” or with “Mon=1”, and you show in principle how both variants can be treated by the same formula concerning the very special question posted here. This basically doesn’t only work for the thursdays (key 5) but also for different days. The remaining problem is that users won’t use a 0-based numbering.
MOD(something - 1; module) + 1 can do the needed transformation.
Yes, the fact that we can use Mod to determine the day of the week is specific to Calc (Excel), since the date uses the serial number of the day. This is not always the case, for example in databases.
and we should keep in mind that the user could also change the base date, which will break the raw MOD operations on dates
1 Like
… except we regard the offset like in =MOD(Date-Offset-1;7)+1
I hope so. In principle dates actually are stored in Calc files like they are communicated: in ISO 8601 extended. The displayed format is stored in addition.
That (ISO…) is the definite format: Only this format is standardized by the entrusted institution, is easily readable by both human and machine, and also says quite clearly “I am a date”.
(The ‘nulldate’ hokum only comes around the corner when the persistent format is converted to DoubleFloat for the “living” sheet. Concerning the weekday question we only need to know the weekday result for any fix date in what way ever given unambiguously as long as every week has 7 days.)
The remaining ambiguity concerning the time zone is due to facts we can’t easily change. Where absolute unambiguity is indispensable we can use UTC time and date, and apply zone offsets as needed.
This is not quite serious. But there are cases now and then where we don’t find a way out of a jungle we stepped in - and one way to get a counter result even in the jungle is to simply count.
disask86374NoDoubtLeft_TheyAreCounted.ods (20.1 KB)
1 Like