Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

vba - Get a WeekNumber's end date using excel macro

I have 3 columns: Year, Weeknum, WeekRange. i'd like the WeekRange column to display the Start and End Dates based on the Year and WeekNum values. I found a code that calculates the Start Date and it works fine but i cant find anything that shows how to get the End Date.

here's the code i found(assuming the WeekNumber is 4 and the Year is 2020)

Function WeekStartDate(Optional intMonth As Integer = 1, _
Optional intDay As Integer = 1)

Dim FromDate As Date, lngAdd As Long
Dim WKDay, WDays As Integer

Dim intWeek, intYear As Integer
intWeek = 4
WDays = 0
intYear = 2020

'Calculating the date
FromDate = DateSerial(intYear, intMonth, intDay)


'Getting the week day # of the specified date considering monday as first day
WKDay = WeekDay(FromDate, vbMonday)

'If value of week day is greater than 4 then subtracting 1 from the week number
If WKDay > 4 Then
    WDays = (7 * intWeek) - WKDay + 1
Else
    WDays = (7 * (intWeek - 1)) - WKDay + 1
End If

'Return the first day of the week`enter code here`
WeekStartDate = FromDate + WDays

Appreciate any help i can get.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Here is a formula solution (no VBA required).

Assuming:

Cell A2 has the year.

Cell B2 has the week number.

Use this formula to get the WeekRange...

=TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,1) + 1,"mm/dd/yy") & " - " & TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,1) + 7,"mm/dd/yy")

The above also assumes you prefer the start of the week to be Sunday. If you would rather the start of the week be Monday then use this instead...

=TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,2) + 1,"mm/dd/yy") & " - " & TEXT(DATE(A2,1,1)+7*B2 - WEEKDAY(DATE(A2,1,1)+7*B2,2) + 7,"mm/dd/yy")

Finally, you can change the format of the date by adjusting the occurrences of "mm/dd/yy" to suit your need.

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...