使用Excel宏获取WeekNumber的结束日期 [英] Get a WeekNumber's end date using excel macro

问题描述

我有3列:Year，Weeknum，WeekRange.我希望WeekRange列显示基于Year和WeekNum值的开始和结束日期.我找到了一个计算开始日期的代码，它可以正常工作，但是我找不到任何显示如何获取结束日期的代码.

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.

这是我找到的代码(假设WeekNumber为4，Year为2020)

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.

推荐答案

这是一个公式解决方案(不需要VBA).

Here is a formula solution (no VBA required).

假设:

单元格 A2 有年份.

单元格 B2 有星期号.

使用此公式获取WeekRange ...

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

最后，您可以通过调整"mm/dd/yy" 的出现来更改日期的格式，以适合您的需求.

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

这篇关于使用Excel宏获取WeekNumber的结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！