在Excel中创建一周中第一天，第二天或第三天的日期

问题描述

我需要确定给定数字是星期几，

I need to determine what day of the week a given number is,

例如我的年份= 2018日历周= 51并且数字= 3(这是一周的第3天)

For E.g. I have Year = 2018 Calendar Week = 51 and Number = 3 (This is 3rd day of the week)

日历第51周发生在12月.

Calendar week 51 falls in the month of December

一周从星期一到星期日开始

Week starts from Monday to Sunday

由于第51周的第3天是19日，结果应为"19/12/2018" .

Since 3rd day in week 51 is 19th, the result should be "19/12/2018".

我们可以用Excel公式还是VBA宏吗?

Could we do it excel formula or VBA macro.

在这方面的任何帮助深表赞赏.

Any help in this regard deeply appreciated.

谢谢，加内什

推荐答案

它是由vba获得的.一个是宏，一个是udf.

It is obtained by vba. One is a macro and one is udf.

宏

Sub getWeekDays()
    Dim vDB, vS(), vR()
    Dim y As Integer, s As Date, e As Date
    Dim i As Integer, k As Integer
    Dim n As Integer
    'y = Year
    y = InputBox("Input year : 2018  ")

    s = DateSerial(y, 1, 0)
    e = DateSerial(y + 1, 1, 0)
    n = e - s
    For i = 1 To n
        d = s + i
        If DatePart("ww", d, vbMonday) = DatePart("ww", d + 1, vbMonday) Then
        Else
            k = k + 1
            ReDim Preserve vR(1 To 2, 1 To k)
            vR(1, k) = DatePart("ww", d, vbMonday) & " Wk"
            If k = 1 Then
                vR(1, k) = DatePart("ww", s + 1, vbMonday) & " Wk"
                vR(2, k) = Format(s + 1, "dd.mm.yyyy") & "~" & Format(d, "dd.mm.yyyy")
            Else
                vR(2, k) = Format(d - 6, "dd.mm.yyyy") & "~" & Format(d, "dd.mm.yyyy")
            End If
        End If
    Next i
    Range("a1").CurrentRegion.Clear
    Range("a1").Resize(k, 2) = WorksheetFunction.Transpose(vR)
End Sub

UDF

Function getWeekDay(y As Integer, WeekNum As Integer, DayOrder As Integer)
    Dim s As Date, e As Date
    Dim i As Integer
    Dim n As Integer
    Application.Volatile
    'WeekNum = 51
    'y = Year
    'DayOrder <~~ If third day  DayOrder = 3

    s = DateSerial(y, 1, 0)
    e = DateSerial(y + 1, 1, 0)
    n = e - s
    For i = 1 To n
        d = s + i

        If DatePart("ww", d, vbMonday) = WeekNum Then
            getWeekDay = d + DayOrder - 1
            Exit Function
        End If
    Next i
End Function

这篇关于在Excel中创建一周中第一天，第二天或第三天的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！