VBA转换周数(和年份)到日期? [英] vba convert week number (and year) to date?

问题描述

我在单元格C13中有一个周号，在单元格C14中有一个年.

I have a week number in Cell C13 and a year in Cell C14.

我正在使用以下公式将其转换为该星期几的日期:

I am using the below formula to convert this into the Thursday of that week number's date:

=DATE(C14,1,-6)-WEEKDAY(DATE(C14,1,3))+C13*7

我如何使用VBA做同样的事情?

How could i do the same thing using VBA?

推荐答案

在特定年份，您可以这样操作:

DateAdd("ww", WeekNumber - 1, DateSerial(2017, 1, 5))

并对其进行测试:

Debug.Print Format(DateAdd("ww", WeekNumber - 1, DateSerial(YearNumber, 1, 5)), "ddd d MMM yy")

---

如果其他人正在调查并且不希望有星期四或在2017年不工作:

1. 更改5以适合您的需求！

1. change the 5 to fit your need!

或使用以下功能GetDayFromWeekNumber

代码进行测试:

Sub test()
    Debug.Print Format(GetDayFromWeekNumber(2017, 1, 4), "ddd d MMM yyyy")
End Sub

---

和通用函数GetDayFromWeekNumber :

---

And the generic function GetDayFromWeekNumber :

Public Function GetDayFromWeekNumber(InYear As Integer, _
                WeekNumber As Integer, _
                Optional DayInWeek1Monday7Sunday As Integer = 1) As Date
    Dim i As Integer: i = 1
    If DayInWeek1Monday7Sunday < 1 Or DayInWeek1Monday7Sunday > 7 Then
        MsgBox "Please input between 1 and 7 for the argument :" & vbCrLf & _
                "DayInWeek1Monday7Sunday!", vbOKOnly + vbCritical
        'Function will return 30/12/1899 if you don't use a good DayInWeek1Monday7Sunday
        Exit Function
    Else
    End If

    Do While Weekday(DateSerial(InYear, 1, i), vbMonday) <> DayInWeek1Monday7Sunday
        i = i + 1
    Loop

    GetDayFromWeekNumber = DateAdd("ww", WeekNumber - 1, DateSerial(InYear, 1, i))
End Function

这篇关于VBA转换周数(和年份)到日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！