excel vba转换周数(和年)到日期?

tv6aics1  于 2023-04-22  发布在  其他
关注(0)|答案(4)|浏览(144)

我在单元格C13中有周数,在单元格C14中有年数
我使用下面的公式将其转换为该星期的星期四**数字的日期:

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

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

e5nszbig

e5nszbig1#

对于特定年份,您可以这样做:

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.或者使用下面的函数GetDayFromWeekNumber
    测试代码:
Sub test()
    Debug.Print Format(GetDayFromWeekNumber(2017, 1, 4), "ddd d MMM yyyy")
End Sub

以及泛型函数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
zsbz8rwp

zsbz8rwp2#

它工作得很好,考虑到一周的第一天是星期天:

Function fnDateFromWeek(ByVal iYear As Integer, ByVal iWeek As Integer, ByVal iWeekDday As Integer)

        fnDateFromWeek = DateSerial(iYear, 1, ((iWeek - 1) * 7) + iWeekDday - Weekday(DateSerial(iYear, 1, 1)) + 1)

End Function

你必须传递你想要的哪一天作为第三个参数。星期四是第5天。感谢这些家伙:http://www.dreamincode.net/forums/topic/111464-calculate-date-from-year-weeknr-and-daynumber/

c2e8gylq

c2e8gylq3#

作为一个附加选项,对于那些使用ISO周数系统的人,其中一年的第一周是一年中包含四天的第一周(或包括日历年的第一个星期四的一周,并且一周的第一天是星期日)。

  • DOW是一个可选的参数,表示星期几。它默认为星期四,因为我们使用的是ISO周数系统,所以应该总是在当前年份内。
Option Explicit
Function WNtoDate(WN As Long, YR As Long, Optional DOW As Long = 5) As Date
    'DOW:  1=SUN, 2=MON, etc
    Dim DY1 As Date
    Dim Wk1DT1 As Date
    Dim I As Long

DY1 = DateSerial(YR, 1, 1)
'Use ISO weeknumber system
I = DatePart("ww", DY1, vbSunday, vbFirstFourDays)

'Sunday of Week 1
Wk1DT1 = DateAdd("d", -Weekday(DY1), DY1 + 1 + IIf(I > 1, 7, 0))

WNtoDate = DateAdd("ww", WN - 1, Wk1DT1) + DOW - 1

End Function
zzlelutf

zzlelutf4#

这是为像我这样没有太多编程经验的人准备的。

Sub DatesForWeekNum()
Dim WeekNumYear As Integer
    WeekNumYear = ActiveSheet.Range("C14").Value

Dim WeekNum As Integer
    WeekNum = ActiveSheet.Range("C13").Value

Dim StartDateYear As Date
    StartDateYear = DateSerial(WeekNumYear, 1, 1)

Dim WeekDayIndex As Integer
    WeekDayIndex = (StartDateYear - 1) Mod 7 'If your week starts on Sunday
    'WeekDayIndex = (StartDateYear - 2) Mod 7 'If your week starts on Monday

Dim DateOfFirstSunday As Date
    If WeekDayIndex < 4 Then
        DateOfFirstSunday = StartDateYear - WeekDayIndex
    Else
        DateOfFirstSunday = StartDateYear - WeekDayIndex + 7
    End If

Dim StartDateWeekNum As Date
    StartDateWeekNum = DateOfFirstSunday + ((WeekNum - 1) * 7)

Dim EndDateWeekNum As Date
    EndDateWeekNum = DateOfFirstSunday + ((WeekNum - 1) * 7) + 7
MsgBox StartDateWeekNum & vbCrLf & EndDateWeekNum End Sub

相关问题