MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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!