上周一 Excel 的日期 [英] Date for Previous Monday Excel

问题描述

今天是 2013 年 2 月 27 日，也就是温斯日.我需要一个公式来返回上一个星期一的日期.这将是 (02/17/2013)

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)

我需要这样我才能在发送电子邮件的 vba 代码中使用文件名或电子邮件主题.

I need to so I can use for file name or email subject in my vba code which sends emails.

With oMail
     'Uncomment the line below to hard code a recipient
     .To = "[e-mail]"
     'Uncomment the line below to hard code a subject
     .Subject = "Current Report"
     .Attachments.Add WB.FullName
    .Display
End With

推荐答案

Public Function LastMonday(pdat As Date) As Date
    LastMonday = DateAdd("ww", -1, pdat - (Weekday(pdat, vbMonday) - 1))
End Function

Weekday(yourdate, vbMonday) 为星期一返回 1，为星期二返回 2，等等

Weekday(yourdate, vbMonday) returns a 1 for Monday, 2 for Tuesday, etc. so

pdat - (Weekday(pdat, vbMonday) - 1)

将通过从过去的日期中减去 Weekday()-1 # 天来为我们提供最近的星期一.

Will give us the most recent Monday by subtracting the Weekday()-1 # of days from the passed date.

DateAdd("ww", -1, ...)

从该日期减去一周.

LastMonday(cdate("2/27/13"))

返回 2/18/2013(这是星期一，而不是 17 日)

Returns 2/18/2013 (which is Monday, not the 17th)

这篇关于上周一 Excel 的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！