View original (French)

Number of weeks in a year

Solved/Closed
bosche -  
 mercibeaucoup -
Hello,
How to calculate in Excel the number of weeks in a year knowing that 2008 is a leap year?
Thank you
Configuration: Windows XP Internet Explorer 7.0

7 réponses

Réponse 1 / 7
chtilou Posted messages 1704 Status Membre 523
 
Hello,

the simplest thing is to ask our friend Excel what he thinks. ;-)

Enter the date in A1
in B1 insert the formula =WEEKNUM(A1)

Suspense...
79
m@rina Posted messages 27328 Registration date   Status Modérateur Last intervention   11 546
 
Hello chtilou,

Except that Excel only knows the American standard!... :(
For the American standard, week 1 is the first week whether it's complete or not.
Whereas for the European standard, week 1 is the first week that has at least 4 days... So it must start on a Thursday or later.
So, for this year and even for next year, it's correct... But... for 2010, the first week will start on a Friday, and then Excel will be completely wrong in our regions!!! We've been telling Microsoft for so long, and they don't care at all about our little week stories!!! ;))

m@rina
0
chtilou Posted messages 1704 Status Membre 523 > m@rina Posted messages 27328 Registration date   Status Modérateur Last intervention  
 
Hi Marina,

I’ll try to remember this info. You never know... ;-)
0
AgriBases > m@rina Posted messages 27328 Registration date   Status Modérateur Last intervention  
 
A solution might be to use software written by programmers who actually care.
In OpenOffice, No.week(Saturday, January 2, 2010) = 53!
0
Réponse 2 / 7
Nep
 
Hello

A week always starts on a Monday, and therefore, even though the number of days is always roughly the same, the number of weeks varies between 52 and 53.
In fact, according to European standards, a week is counted in the current year if it has at least 4 days in that year.
Thus, if December 28 is a Monday, Tuesday, or Wednesday, then the current year has 53 weeks... (since the last week will count 4, 5, or 6 days)

In terms of algorithmic code:
Calculate the Monday of the week of December 28
If this Monday == December 28 or December 27 or December 26 ==> return 53 weeks
Otherwise return 52 weeks

Hoping to have helped a bit...

Nep

-----------------------------------------------------------------------------------------------
37
Réponse 3 / 7
Astérix
 
Following everything I've read, I relied on the definitions from Larousse and the established formulas from Excel. Leap years occur every 4 years when the year is divisible by 4, divisible by 100 if divisible by 400: 2000, 1700, 1800, and 1900 are leap years. =IF(RIGHT(TEXT(janv!$D$11,"aaaa"),2)="00",IF(RIGHT(TEXT(janv!$D$11,"aaaa")/100/400,2)="05",29,28),IF(RIGHT(TEXT(janv!$D$11,"aaaa")/4*100,2)="00",29,28)). You enter the date of January 1st in a chosen cell, e.g., 1/01/2010, the result of the formula will give you either 28 or 29 days for the month of February. For the number of weeks IF(RIGHT(TEXT(janv!$D$11,"aaaa")/6*100,2)="00",53,52), the result of the formula will give you either 52 or 53. These values are stored in cells located in a sheet that I call parameters and are used for calendar calculations.

To find out the days of the week 52 or 53 or 1, I created the following formulas using the Excel formula WEEKDAY(janv!D11,2). Note: D11 is the date of January 1, 1/01/10.
Private Sub Worksheet_Change(ByVal Target As Range)

If (Target.Address = "$D$11") Then
Worksheets(3).Unprotect "TSA3X8"
If Sheets(1).Range("A14").Value = 1 Then
Sheets(3).Range("F14").Value = Sheets(3).Range("D11").Value
Sheets(3).Range("H14").Value = Sheets(3).Range("D11").Value + 1
Sheets(3).Range("J14").Value = Sheets(3).Range("D11").Value + 2
Sheets(3).Range("L14").Value = Sheets(3).Range("D11").Value + 3
Sheets(3).Range("N14").Value = Sheets(3).Range("D11").Value + 4
Sheets(3).Range("P14").Value = Sheets(3).Range("D11").Value + 5
Sheets(3).Range("R14").Value = Sheets(3).Range("D11").Value + 6
Sheets(3).Range("F12").Value = Sheets(3).Range("D11").Value - Sheets(3).Range("F14").Value
ElseIf Sheets(1).Range("A14").Value = 2 Then
Sheets(3).Range("F14").Value = Sheets(3).Range("D11").Value - 1
Sheets(3).Range("H14").Value = Sheets(3).Range("D11").Value
Sheets(3).Range("J14").Value = Sheets(3).Range("D11").Value + 1
Sheets(3).Range("L14").Value = Sheets(3).Range("D11").Value + 2
Sheets(3).Range("N14").Value = Sheets(3).Range("D11").Value + 3
Sheets(3).Range("P14").Value = Sheets(3).Range("D11").Value + 4
Sheets(3).Range("R14").Value = Sheets(3).Range("D11").Value + 5
Sheets(3).Range("F12").Value = Sheets(3).Range("D11").Value - Sheets(3).Range("F14").Value
ElseIf Sheets(1).Range("A14").Value = 3 Then
Sheets(3).Range("F14").Value = Sheets(3).Range("D11").Value - 2
Sheets(3).Range("H14").Value = Sheets(3).Range("D11").Value - 1
Sheets(3).Range("J14").Value = Sheets(3).Range("D11").Value
Sheets(3).Range("L14").Value = Sheets(3).Range("D11").Value + 1
Sheets(3).Range("N14").Value = Sheets(3).Range("D11").Value + 2
Sheets(3).Range("P14").Value = Sheets(3).Range("D11").Value + 3
Sheets(3).Range("R14").Value = Sheets(3).Range("D11").Value + 4
Sheets(3).Range("F12").Value = Sheets(3).Range("D11").Value - Sheets(3).Range("F14").Value
ElseIf Sheets(1).Range("A14").Value = 4 Then
Sheets(3).Range("F14").Value = Sheets(3).Range("D11").Value - 3
Sheets(3).Range("H14").Value = Sheets(3).Range("D11").Value - 2
Sheets(3).Range("J14").Value = Sheets(3).Range("D11").Value - 1
Sheets(3).Range("L14").Value = Sheets(3).Range("D11").Value
Sheets(3).Range("N14").Value = Sheets(3).Range("D11").Value + 1
Sheets(3).Range("P14").Value = Sheets(3).Range("D11").Value + 2
Sheets(3).Range("R14").Value = Sheets(3).Range("D11").Value + 3
Sheets(3).Range("F12").Value = Sheets(3).Range("D11").Value - Sheets(3).Range("F14").Value
ElseIf Sheets(1).Range("A14").Value = 5 Then
Sheets(3).Range("F14").Value = Sheets(3).Range("D11").Value - 4
Sheets(3).Range("H14").Value = Sheets(3).Range("D11").Value - 3
Sheets(3).Range("J14").Value = Sheets(3).Range("D11").Value - 2
Sheets(3).Range("L14").Value = Sheets(3).Range("D11").Value - 1
Sheets(3).Range("N14").Value = Sheets(3).Range("D11").Value
Sheets(3).Range("P14").Value = Sheets(3).Range("D11").Value + 1
Sheets(3).Range("R14").Value = Sheets(3).Range("D11").Value + 2
Sheets(3).Range("F12").Value = Sheets(3).Range("D11").Value - Sheets(3).Range("F14").Value
ElseIf Sheets(1).Range("A14").Value = 6 Then
Sheets(3).Range("F14").Value = Sheets(3).Range("D11").Value - 5
Sheets(3).Range("H14").Value = Sheets(3).Range("D11").Value - 4
Sheets(3).Range("J14").Value = Sheets(3).Range("D11").Value - 3
Sheets(3).Range("L14").Value = Sheets(3).Range("D11").Value - 2
Sheets(3).Range("N14").Value = Sheets(3).Range("D11").Value - 1
Sheets(3).Range("P14").Value = Sheets(3).Range("D11").Value
Sheets(3).Range("R14").Value = Sheets(3).Range("D11").Value + 1
Sheets(3).Range("F12").Value = Sheets(3).Range("D11").Value - Sheets(3).Range("F14").Value
ElseIf Sheets(1).Range("A14").Value = 7 Then
Sheets(3).Range("F14").Value = Sheets(3).Range("D11").Value - 6
Sheets(3).Range("H14").Value = Sheets(3).Range("D11").Value - 5
Sheets(3).Range("J14").Value = Sheets(3).Range("D11").Value - 4
Sheets(3).Range("L14").Value = Sheets(3).Range("D11").Value - 3
Sheets(3).Range("N14").Value = Sheets(3).Range("D11").Value - 2
Sheets(3).Range("P14").Value = Sheets(3).Range("D11").Value - 1
Sheets(3).Range("R14").Value = Sheets(3).Range("D11").Value
Sheets(3).Range("F12").Value = Sheets(3).Range("D11").Value - Sheets(3).Range("F14").Value
End If
If Sheets(1).Range("A14").Value = 7 Then 'Di
Sheets(3).Range("N12").Value = Sheets(1).Range("I8").Value
ElseIf Sheets(1).Range("A14").Value = 6 And Sheets(1).Range("I8").Value = 52 Then 'Sa
Sheets(3).Range("N12").Value = 1
ElseIf Sheets(1).Range("A14").Value = 6 And Sheets(1).Range("I8").Value = 53 Then
Sheets(3).Range("N12").Value = Sheets(1).Range("I8").Value
ElseIf Sheets(1).Range("A14").Value <> 6 Or Sheets(1).Range("A14").Value <> 7 Then
Sheets(3).Range("N12").Value = 1
End If
Worksheets(3).Protect "TSA3X8"
End If
End Sub
This whole setup may seem a bit heavy for some, but it's fast and works wonderfully for managing 3x8 service shifts for a large number of agents throughout the year, and I should point out that I’m not a professional in VBA.
Best regards
Best regards
21
Réponse 4 / 7
Xavstarblues Posted messages 10585 Registration date   Status Contributeur Last intervention   1 858
 
Hello
number of days in 2008 = 366
number of days in a week = 7
366/7=
52.285714285714285714285714285714
......
I’ll let you round it off
--
P4 2.4, 512 DDR, Geforce 6200 AGP Win XP SP2|||P3 1 GHZ, 256 SDRAM, Debian Etch ||| P3 1Ghz, 392 SDRAM, Xubuntu 6.10
AMD Sempron 2600+, 768 DRR, Win 2k|||AMD Athlon 64 X2 4200+, 2 Go DDR Ubuntu, Suse or Solaris not chosen yet
15
Réponse 5 / 7
Axtérix24
 
=IF(RIGHT($G$3/6*100,2)="00",53,1)
HERE IS A FORMULA THAT WILL MAKE YOUR LIFE EASIER.
$G$3 IS THE YEAR NUMBER LOCATED IN THIS CELL
Best regards
14
Cyrano75
 
Hello everyone,

I think the formula above:
=IF(RIGHT($G$3/6*100;2)="00",53,1)
only serves to find the week number of January 1st.
It's not flexible and transposable across all tables, as it should only be applied to the week of January 1st.

For every day of the year, I found this formula online for Excel.
It’s not my own but works flawlessly in many of my tables.
I've tested and validated it.

- in A1: enter the date to test
- in B1: paste the following formula:
=INT(MOD(INT((A1-2)/7)+0,6;52+5/28))+1

=> The week number in question will be displayed in B1.
And thus the value 53 for 01/01/2010 ...
And week 1 for 04/01/2010, which is consistent with our French calendar.

It advantageously replaces the generic Excel formula
=WEEKNUM(A1;2)
This formula valid for American calendars indicates
- week 53 from Monday, December 28 to Thursday, December 31, 2009
- week 01 from Friday, January 1 to Sunday, January 3, 2010
- week 02 starting Monday, January 4, 2010.

I need someone to explain to me how the week number changes between Thursday and Friday...

Fred
0
Réponse 6 / 7
scorpionfred Posted messages 329 Status Membre 42
 
There are 52 weeks (365 days or 366 days divided by 7):

52 in 2000
52 in 2001
52 in 2002
52 in 2003
52 in 2004
52 in 2005
52 in 2006
52 in 2007
52 in 2008
52 in 2009
52 in 2010
52 in 2011
52 in 2012
52 in 2013
52 in 2014
52 in 2015
52 in 2016
52 in 2017
52 in 2018
52 in 2019
52 in 2020
52 in 2021
52 in 2022
52 in 2023
52 in 2024
52 in 2025
52 in 2026
52 in 2027
52 in 2028
52 in 2029
52 in 2030
10
Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention   17 475
 
52 in 2000
52 in 2001
52 in 2002
52 in 2003
52 in 2004
52 in 2005
52 in 2006
52 in 2007
52 in 2008
52 in 2009
52 in 2010
52 in 2011
52 in 2012
52 in 2013
...
Are you reassured now?
0
Ardi
 
Or even simpler, you do 364.25 / 7 ... lol
0
Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention   17 475
 
Fortunately, you were there: Nobody would have thought of it, can you believe it!

Except maybe Xavstarblues: https://forums.commentcamarche.net/forum/affich-6616057-nombre-de-semaines-dans-une-annee#2
0
Toni
 
Oh yeah, 52 in ...
thanks!
0
Jtd
 
You're welcome.
0
Réponse 7 / 7
Raymond PENTIER Posted messages 58550 Registration date   Status Contributeur Last intervention   17 475
  
A leap year is a year of 366 days instead of 365, meaning a year that includes a February 29. The term comes from the Latin bis-sextilis, which means "twice (bis) sixth (sextus)." Since the introduction of the Gregorian calendar, leap years are those that are divisible by 4 but not divisible by 100, or divisible by 400. Thus, the year 1900 was not a leap year because it is divisible by 100 and not divisible by 400. The year 2000 was a leap year because it is divisible by 400. The Julian calendar had an average year of 365.25 days, instead of the 365.2422 days needed for the Earth's cycle. This caused an accumulation of about ten days of delay over fifteen centuries. This delay was corrected by removing days.
https://www.techno-science.net/definition/3052.html

My suit, on the other hand, is bitextile, as it is made from a mixed wool and linen fabric.
6