VBA to return the date of the Friday after a given date
Posted by RichT on January 23, 2002 12:59 PM
I've been searching the old posts for clues to this solution with no luck...
I have rows of sales information with sale date in the first column. How can I use VBA to take a date (say 1/2/02), and determine that the date of the Friday of that week would be 1/4/02? The raw and converted dates would look like...
Mon 12/31/01 -> 1/4/02
Tue 1/1/02 -> 1/4/02
Wed 1/2/02 -> 1/4/02
Thur 1/3/02 -> 1/4/02
Fri 1/4/02 -> 1/4/02
Thanks in advance for your suggestions
Posted by Mark O'Brien on January 23, 2002 1:53 PM
This should work, I've checked it with a few random dates. If you've got any problems, just repost.
Public Sub Main()
Dim MyFriday As Date
Dim MyDate As Date
MyDate = #1/2/2002#
MyFriday = NextFriday(MyDate)
MsgBox MyFriday
End Sub
Private Function NextFriday(MyDate As Date) As Date
Dim MyDay As Integer
MyDay = Weekday(MyDate, vbSunday)
Delta = vbFriday - MyDay
NextFriday = MyDate + Delta
End Function
Posted by Juan Pablo G. on January 23, 2002 1:55 PM
Use this UDF.
Function Friday(DateRange As Date) As Date
Friday = DateRange + 7 - WeekDay(DateRange, 7)
End Function
You can use it directly in Excel. Assuming your sample data is in A1:A5, put this formula in B1
=Friday(A1)
and drag down.
Juan Pablo G.
Posted by Mark O'Brien on January 23, 2002 1:57 PM
Nicer tidier version of what I suggested. Damn you JPG. :)
Posted by Juan Pablo G. on January 23, 2002 1:59 PM
I was just seeing what you suggested. I forgot about vbFriday, and can you believe that the Excel function Weekday doesn't accept 7 as a second argument ????? only 0, 1 or 2 !!!
Incredible, same function, works different in VBA and in Excel.
Juan Pablo G.
Posted by Mark O'Brien on January 23, 2002 2:02 PM
I thought that 7 was a bit suspicious, especially as it's not been ten minutes since I was looking in Excel help to see why the "Day" function wasn't doing what I wanted. Then I remembered "Weekday". duh. Oh well.
Posted by RichT on January 24, 2002 11:35 AM
I appreciate your solutions. Not one, but two or three ways to skin a cat. Ooouueee, that's gross!
That solved my problem!
