vba 上周一 Excel 的日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15116513/
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
Date for Previous Monday Excel
提问by Mowgli
Today is 02/27/2013 which is Wensday. I need formula which will return me date for previous Monday.which would be (02/17/2013)
今天是 2013 年 2 月 27 日，也就是温斯日。我需要一个公式来返回上一个星期一的日期。这将是 (02/17/2013)
I need to so I can use for file name or email subject in my vba code which sends emails.
我需要这样我才能在发送电子邮件的 vba 代码中使用文件名或电子邮件主题。
With oMail
'Uncomment the line below to hard code a recipient
.To = "[email protected]"
'Uncomment the line below to hard code a subject
.Subject = "Current Report"
.Attachments.Add WB.FullName
.Display
End With
回答by Dan Metheus
Public Function LastMonday(pdat As Date) As Date
LastMonday = DateAdd("ww", -1, pdat - (Weekday(pdat, vbMonday) - 1))
End Function
Weekday(yourdate, vbMonday) returns a 1 for Monday, 2 for Tuesday, etc. so
Weekday(yourdate, vbMonday) 星期一返回 1，星期二返回 2，依此类推
pdat - (Weekday(pdat, vbMonday) - 1)
Will give us the most recent Monday by subtracting the Weekday()-1 # of days from the passed date.
通过从过去的日期中减去 Weekday()-1 # 天，将为我们提供最近的星期一。
DateAdd("ww", -1, ...)
subtracts one week from that date.
从该日期减去一周。
LastMonday(cdate("2/27/13"))
Returns 2/18/2013 (which is Monday, not the 17th)
返回 2/18/2013（即星期一，而不是 17 日）
回答by Robert Ilbrink
Calculate the difference between Weekday(Now()) and 2 (= weekday for monday), then add 7.
计算 Weekday(Now()) 和 2（= 星期一的工作日）之间的差，然后加上 7。
回答by Our Man in Bananas
Dan's answer should cover your needs in VBA
丹的回答应该涵盖您在 VBA 中的需求
or in Excel worksheet formula, you could do something like this:
或在 Excel 工作表公式中，您可以执行以下操作：
=TEXT(DateCell- (WEEKDAY(DateCell,2)-1),"dddd mmmm dd")
so DateCell is a range containing the date that you want to find the date of the previous Monday!
所以 DateCell 是一个包含您要查找上一个星期一日期的日期的范围！
so if you put 08/04/2012 in DateCell, then that formula will retrun Monday 2nd April!
因此，如果您将 08/04/2012 放入 DateCell，那么该公式将在 4 月 2 日星期一重新运行！
(credit to MrExcel.com and Google search!) HTH Philip
（归功于 MrExcel.com 和 Google 搜索！）HTH Philip
回答by Pete855217
To make the accepted answer's function a bit more versatile, a couple of minor changes lets you specify which day of week, and how far back/forward you want it.
为了使接受的答案的功能更加通用，一些小的更改可让您指定一周中的哪一天，以及您想要它的后退/前进多远。
Public Function LastDow(pdat As Date, dow as integer, _&
optional weeksOffset = -1 as integer) As Date
LastDow = DateAdd("ww", weeksOffset, pdat - (Weekday(pdat, dow) - 1))
End Function
With this function you can get, say, the next Wednesday:
使用这个函数，你可以得到，比如说，下一个星期三：
dim myDt as date
dim nextWed as date
myDt = now()
// Get next Wednesday (dow = Wednesday, weeksOffset is +1
x = LastDow(myDt, vbWednesday, 1)
Thanks again to the original solution author (Dan Meltheus).
再次感谢原解决方案作者 (Dan Meltheus)。
