vba 获得上周五日期的巧妙方法

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/2094762/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:14:47  来源:igfitidea点击:

neat way to get last friday's date

vba

提问by Nick

no matter what day I call the function on.

无论我在哪一天调用该函数。

I know I could write a select case weekday(now) statement, was just wondering if there was a neater way to go?

我知道我可以写一个 select case weekday(now) 语句,只是想知道是否有更简洁的方法?

回答by Alex P

Does this help get you started? I just gave it a quick test and seemed to work ok.

这是否有助于您入门?我只是对其进行了快速测试,似乎工作正常。

Private Sub LastFriday()

    Dim iWeekday As Integer, LastFridayDate As Date

    iWeekday = Weekday(Now(), vbFriday)

    LastFridayDate = Format(Now - (iWeekday - 1), "dd-mmm-yy")

End Sub

回答by Nick

DateAdd("d", -1 - Weekday(Now), Now)

DateAdd("d", -1 - 工作日(现在),现在)

回答by George Johnston

DatePart('dddd', now)

or

或者

DatePart('dddd', #1/1/2010#) 

...with an explicit date.

...有明确的日期。

回答by Pec1983

This function will find every Friday for the last Month, You can change it to suit other days such as Monday "oldDay = 2" etc the method will work from today's date, but you can change that to suit

此函数将查找上个月的每个星期五,您可以将其更改为适合其他日期,例如星期一“oldDay = 2”等该方法将从今天的日期开始工作,但您可以更改它以适应

Dim todaysDate As Date = Date.Today Dim oldDay As Integer Dim thisWeek As Date

Dim todaysDate As Date = Date.Today Dim oldDay As Integer Dim thisWeek As Date

    Dim firstWeek As Date
    Dim secondWeek As Date
    Dim thirdWeek As Date
    Dim fourthWeek As Date


    ''finds the Friday of the end of the current week 
    ''No mattter what day you are working 
    Dim daycount As Integer

    oldDay = Weekday(todaysDate)
    thisWeek = todaysDate

    If oldDay < 6 Then
        daycount = 6 - oldDay
        thisWeek = thisWeek.AddDays(+daycount)
    ElseIf oldDay > 6 Then
        daycount = oldDay - 6
        thisWeek = thisWeek.AddDays(-daycount)
    End If


    firstWeek = thisWeek
    secondWeek = thisWeek
    thirdWeek = thisWeek
    fourthWeek = thisWeek

    fourthWeek = firstWeek.AddDays(-28)
    thirdWeek = thirdWeek.AddDays(-21)
    secondWeek = secondWeek.AddDays(-14)
    firstWeek = firstWeek.AddDays(-7)