In this article, Doug Steele demonstrates a useful formula to use with date calculations.
If I’ve got a date, can I determine the date of the Monday for the week that date is in? For instance, I’ve got a date of 17 November 2004 (which is a Wednesday), and I want to identify that the Monday of that week is 15 November 2004.
In my September column (which dealt with calculating working days), I alluded to how the Weekday function works, but I’m not sure I really spelled out the details.
Given a date, the Weekday function returns a value between 1 and 7 to represent what day of the week the date represents. There’s an optional second parameter (FirstDayOfWeek) that you can pass to the function, though, that changes what day each of the numbers 1 through 7 actually represents.
By default, the Weekday function assumes that the week starts on Sunday, so that Sunday will be day 1, Monday will be day 2, and so on up to Saturday as day 7. You can change that behavior, though. If you want the week to start on Monday, you pass vbMonday (which equals 2) to the function as well as your date, and now Monday will be identified as day 1, Tuesday as day 2, and so on to Saturday as day 6 and Sunday as day 7. This can be very useful.
Because you want to calculate the Monday before a particular date, you’ll need to subtract a certain number of days from that date. If the date is a Monday, you don’t need to subtract anything. If the date is a Tuesday, you need to subtract a day, and so on. That means that a function like this will solve your problem:
Function PrevMonday(DateGiven As Date) As Date PrevMonday = DateAdd("d", _ 1 - Weekday(DateGiven, vbMonday), _ DateGiven) End Function
However, I think it would be better to come up with a more generic function than this. For one thing, you may not always want to know the previous Monday–sometimes you may want, for instance, the previous Saturday or Sunday. As well, sometimes you may want a following day. For instance, if you have a given date, you may want to know the Monday before it and the Friday after your date.
Implementing the first option should be rather obvious: All you need to do is substitute a different value for FirstDayOfWeek in the call to Weekday in the function.
To determine the particular day after the given date, you’ll need to add a certain number of days to the date. It turns out that the number of days that you want to add is 7 days more than whatever you subtracted in the first case, or 8 – Weekday(…). However, there’s a potential problem with this solution. When I calculated the previous Monday earlier, if the date being checked was a Monday, the function simply returned the date itself. Assuming that I want the same behavior when determining the following date, I’d expect that if I ask for the Friday following a given date and I pass a Friday to the function, then I’ll get my date. That means that I want to use 0 instead of 7 when it’s 7 that will be returned by my calculation.
Fitting it all together, the following function does everything I want:
Function RelatedDate( _ DateGiven As Date, _ WhatWeekday As Integer, _ Optional Prev As Boolean = True _ ) As Date If WhatWeekday > 0 And WhatWeekday < 8 Then If PreviousFlag = True Then RelatedDate = DateAdd("d", _ 1 - Weekday(DateGiven, WhatWeekday), _ DateGiven) Else RelatedDate = DateAdd("d", _ (8 - Weekday(DateGiven, WhatWeekday)) _ Mod 7, DateGiven) End If End If End Function
To get the previous Monday to a particular date, call the function as:
dtmPrevMonday = RelatedDate(Date, vbMonday)
To get the following Friday, call the function like this:
dtmNextFriday = _ RelatedDate(Date, vbFriday, False)
If you’d rather the function return the date a week previous when the given date is the weekday in question, first find this line:
1 - Weekday(DateGiven, WhatWeekday)
and then change it to:
IIf((1 - Weekday(DateGiven, WhatWeekday) = 0, _ 7, 1 - Weekday(DateGiven, WhatWeekday))