» Determining whether Two Given Dates Occur within the Same Week
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
Determining whether each pair of corresponding dates in columns A & B occur within the same week.
Solution:
Use the WEEKNUM and IF functions as shown in the following formula:
=IF(WEEKNUM(A2,2)=WEEKNUM(B2,2),"Same Week","Different Week")
Book Store:
Recommended Books:
- Keys to Reading an Annual Report (Barron's Business Keys)
- Excel 2002 Power Programming with VBA
- What the IRS Doesn't Want You to Know: A Cpa Reveals the Tricks of the Trade
- F1 Get the Most out of Excel! The Ultimate Excel tip Help Guide
- Windows XP All-in-One Desk Reference For Dummies
- Microsoft PowerPoint Version 2002 Step by Step
This is not correct.
I wrote the following function WkNr() to return the corect weeknumber.
The rule as is stated in The Netherlands is the following:
When January 1 falls on or after Thursday Week 1 is the next Monday
When January 1 falls on or before Wednesday the previous Monday is week 1
Public Function WkNr(wdate As Date) As Date
Dim wk As Integer
Dim wd As Integer
Dim yr As Integer
Dim tDate, ttdate As Date
yr = Year(wdate)
tDate = DateValue("01-01-" & yr)
ttdate = tDate
wd = Weekday(tDate)
Select Case wd
Case 1
tDate = tDate + 1
Case 2
tDate = tDate + 0
Case 3
tDate = tDate - 1
Case 4
tDate = tDate - 2
Case 5
tDate = tDate + 4
Case 6
tDate = tDate + 3
Case 7
tDate = tDate + 2
End Select
If Int((wdate - tDate) / 7) + 1 > 0 Then
If ((wdate = DateValue("30-12-" & yr) And Int((wdate - tDate) / 7) + 1 = 53 And (Weekday(DateValue("30-12-" & yr)) = 2)) Or (wdate = DateValue("31-12-" & yr) And Int((wdate - tDate) / 7) + 1 = 53 And (Weekday(DateValue("31-12-" & yr)) = 2 Or Weekday(DateValue("31-12-" & yr)) = 3))) Then
WkNr = 1
Else
WkNr = Int((wdate - tDate) / 7) + 1
End If
Else
WkNr = WkNr(wdate - Day(wdate))
End If
End Function
This works fine.
Enjoy it.
Hans