Count Thursdays in year

English
#1

Thanks in advance to all of you smart people… Using LO Calc 7.2.7.2 on Windows 11.

Feel like a dummy for asking, but have a list of year(s) in column A (A38:A49), and want to put number of Thursdays in that year in column B.

I’ve got:
ROW A B
38 2016
39 2017
40 2018
41 2019
42 2020

And I want:
ROW A B
38 2016 53
39 2017 52
40 2018 52
41 2019 52
42 2020 53

What formula can I use to get this? I found a formula online (from a forum member - THANK YOU!), but it requires the start and end date(s) include month and year (which isn’t the format I was given), and that the date(s) are external to the formula. How can I do this with the date IN the formula?

1 Like
#3

Please always refer to the found information. Which was that formula? If you referenced it, you both would credit that “forum member” properly, and also allow others to start from something substantial, only proposing you changes that allowed to make it work in your case.

#20

Do you ask for

  1. Count of Thursdays between 1st of January and 31st of December?
  2. Count of Thursdays between week 1 and the last week (which is the count of weeks)?
    2.1. First week is the one with the 1st of January
    2.1.1. Fist day of that week is Sunday
    2.1.2. First day of that weeks is Monday
    2.2. First week is the one with the 4th of January (the week with more days in this year)
    2.2.1. Fist day of that week is Sunday
    2.2.2. First day of that weeks is Monday

WEEKSINYEAR calculates the weeks according to 2.2.2.
=INT((DATE($A2;12;31)-(DATE($A4;1;1)+MOD(5-WEEKDAY(DATE($A2;1;1));7)))/7)+1 might be a valid solution for point 1 where A2 is the year number and 5 stands for Thursday.
P.S.: and all the cases between 1. and 2.2.2 should be handled by Lupp’s formulas in Count Thursdays in year - #22 by Lupp

#24

Which, incidentally, is exactly equal to #1.

#2

Use WEEKSINYEAR, which counts only weeks that have most days in the year, which means their Thursdays must be in this year:

=WEEKSINYEAR(DATE(A1;1;10))

Or manually:

=CHOOSE(WEEKDAY(DATE(A1;1;1);2);52;52;IF(ISLEAPYEAR(DATE(A1;1;1));53;52);53;52;52;52)
#4

By the way, who told you that there were 53 Thursdays in 2016? There were 52 :wink:

#5

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.

#6

Well, try NETWORKDAYS function:
=NETWORKDAYS(DATE(B38;1;1);DATE(B38;12;31);;{1;1;1;1;0;1;1})

image

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
#8
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
#10

@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.

#11

=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)

#12

So you claim that the phrase “2015-12-31 was the first Thursday in 2016” makes sense? :wink: Note that it is orthogonal to week numbering.

#13

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.

#14

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.

#15

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.

#19

Ah! Thank you! And likely, I need to invent an excuse, like “I need some sleep”, to explain my stupidity :smiley:

#25

Some reading for the night:

So all we discuss relies on the current calender…

#7

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
#9

It is possible without this function: :slightly_smiling_face:

=IF(OR(MOD(DATE(A1;1;1);7)=5;MOD(DATE(A1;12;31);7)=5);53;52)
1 Like
#16

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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.