Check for every Friday of the week.

[donlincolnmre2]

Hello

I’m looking to modify this macro which shows the date on upcoming Friday so in this case it will be 04/01/22

What I’m looking for is to place this Friday date in Cell A1 and then the macro will check every day for that day, so e.g if today is Tuesday and Friday is in future then the macro will just abort so same thing with wed and Thursday and If Friday is = to Friday then macro should also abort.

Once its Sat then the date in cell A1 is for Friday so the macro should put the new upcoming Friday date and keep checking as above for each day.

Below is the code.

Any help would be greatly appreciated.

Thanks.

Sub VBA_Find_Next_Friday_Method1()

Dim dNext_Friday As Date

dNext_Friday = DateAdd("d", -Weekday(Now) + 13, Now)

MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & _
" Next Friday Date is : " & Format(dNext_Friday, "DD MMM YYYY"), vbInformation, "Next Friday Date"

End Sub

 

[BSALV]

Sub VBA_Find_Next_Friday_Method1()
  
    For i = 1 To 14
          mydate = Date + i
          weekdag = Weekday(mydate, vbFriday)                   'weekday when the week starts on friday
          dNext_Friday = mydate + IIf(weekdag = 1, 0, (8 - weekdag))
          MsgBox "if today is " & Format(mydate, "ddd dd mm yyyy") & vbLf & " then next friday is " & Format(dNext_Friday, "ddd dd/mm/yyyy")
     Next
    
End Sub

Add 7 to dNext_Friday is it has to be 1 week later

 

[Fluff]

Rather than a macro you could just put this in A1 & it will show next Friday's date

=WORKDAY.INTL(TODAY()-1,1,"1111011")

 

[donlincolnmre2]

=WORKDAY.INTL(TODAY()-1,1,"1111011")

Above formula is not working,

Sub VBA_Find_Next_Friday_Method1()
 
    For i = 1 To 14
          mydate = Date + i
          weekdag = Weekday(mydate, vbFriday)                   'weekday when the week starts on friday
          dNext_Friday = mydate + IIf(weekdag = 1, 0, (8 - weekdag))
          MsgBox "if today is " & Format(mydate, "ddd dd mm yyyy") & vbLf & " then next friday is " & Format(dNext_Friday, "ddd dd/mm/yyyy")
     Next
   
End Sub

Add 7 to dNext_Friday is it has to be 1 week later

Hello

I ran the macro and its showing the date in a TEXT BOX, instead of PUTTING VALUE IN CELL A1, (which will be Friday Date), my computer clock shows today is 3/25 but macro is saying if today is sat 26 03 2022 and there is a bug in there.

Since i'm doing some calculation base on Friday, then that Friday date has to be in cell A1 and then i can write calculations on it. Below is what i posted earlier.

What I’m looking for is to place this Friday date in Cell A1 and then the macro will check every day for that day, so e.g if today is Tuesday and Friday is in future then the macro will just abort so same thing with wed and Thursday and If Friday is = to Friday then macro should also abort.

Once its Sat then the date in cell A1 is for Friday so the macro should put the new upcoming Friday date and keep checking as above for each day.

Attached is a picture that shows when i ran the macro today which is FRIDAY, then it showing me wrong information.

Thanks.

 

[Fluff]

Above formula is not working,

In what way?

 

[BSALV]

Map1
	A	B
1	vrijdag 25 maart 2022	25/03/2022
2	vrijdag 25 maart 2022
Blad2

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A1	A1	=next_Friday(B1)
A2	A2	=WORKDAY.INTL(B1-1,1,"1111011")

Sub VBA_Find_Next_Friday_Method1()

     Dim dNext_Friday As Date

     dNext_Friday = Next_Friday(Now)

     MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & " Next Friday Date is : " & Format(dNext_Friday, "DD MMM YYYY"), vbInformation, "Next Friday Date"

End Sub

Function Next_Friday(MyDate)
     weekdag = Weekday(MyDate, vbFriday)                        'weekday when the week starts on friday
     Next_Friday = MyDate + IIf(weekdag = 1, 0, (8 - weekdag))
End Function

 

[donlincolnmre2]

Map1
	A	B
1	vrijdag 25 maart 2022	25/03/2022
2	vrijdag 25 maart 2022
Blad2

Cell Formulas
Range	Formula
B1	B1	=TODAY()
A1	A1	=next_Friday(B1)
A2	A2	=WORKDAY.INTL(B1-1,1,"1111011")

Sub VBA_Find_Next_Friday_Method1()

     Dim dNext_Friday As Date

     dNext_Friday = Next_Friday(Now)

     MsgBox "If today's date is '" & Format(Now, "DD MMM YYYY") & "' then" & vbCrLf & " Next Friday Date is : " & Format(dNext_Friday, "DD MMM YYYY"), vbInformation, "Next Friday Date"

End Sub

Function Next_Friday(MyDate)
     weekdag = Weekday(MyDate, vbFriday)                        'weekday when the week starts on friday
     Next_Friday = MyDate + IIf(weekdag = 1, 0, (8 - weekdag))
End Function

Above macro is working, which is nice, its still putting Friday date of, in this case, 01 Apr 2022 in a Message box, I don't need to see the message if today and..... but i need the date of Friday in CELL A1 in this case it will be 01 Apr 2022 in CELL A1.

Thanks.

 

[BSALV]

RedditCFS.xlsx
	A	B
1	vr 01/04/22
2
Blad1

Cell Formulas
Range	Formula
A1	A1	=next_Friday(TODAY())

That function isn't volatile, so perhaps you must add =today() in a cell somewhere, it doesn't matter where.

 

[donlincolnmre2]

I placed today's date in cell A1 and then the formula in cell B1 and nothing happens. The excel version that i have is Excel 2000.

 

[BSALV]

My Next Friday.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	vr 04/01/2022													03/28/2022 04:45	<---------------- somewhere in your sheet
2	vr 04/01/2022
3
Blad1

Cell Formulas
Range	Formula
N1	N1	=NOW()
A1	A1	=next_friday(N1)
A2	A2	=WORKDAY.INTL(TODAY()-1,1,"1111011")

your friday
Was my UDF(macro) in that workbook ?
Now i added "application volatile" and somewhere in your sheet =now().
If that doesn't work, then use the excel function of @Fluff.

Function Next_Friday(MyDate)
     Application.Volatile
     MyDate = CLng(Date)
     weekdag = Weekday(MyDate, vbFriday)                        'weekday when the week starts on friday
     Next_Friday = MyDate + IIf(weekdag = 1, 0, (8 - weekdag))
End Function