To check if Last working day of month was a Friday.

[rollingzep]

Hi All,

In my Excel App, for our monthly report, I need to set the value of a column based on a date column (B).
If the dates in col B are the last working day of the month and happens to be a Friday then values in col BA should 3.
For example, in Col B, the dates are 03/31/03 and is a Friday, how to derive the value.

Since April 28 will also be the last working day and Friday, i need to get this .
How to determine if the last working day is a Friday or not?

TIA

 

[welshgasman]

Look at the Weekday() function.
Does depend on your starting day.

 

[rollingzep]

Look at the Weekday() function.
Does depend on your starting day.

I tried this but got an error

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Weekday(ws.Range("B" & i).Value = 5, True, False) = True Then
            ws.Range("BG" & i).Value = "3"
        End If
    Next i

 

[welshgasman]

Lookup WeekDay() syntax. ?

 

[Fluff]

Try

If Weekday(ws.Range("B" & i).Value)=6 Then

 

[rollingzep]

Try

If Weekday(ws.Range("B" & i).Value)=6 Then

6 is for Friday?

 

[rollingzep]

6 is for Friday?

Is this correct,

    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Weekday(ws.Range("B" & i).Value) = 6 Then
            ws.Range("BG" & i).Value = "3"
        End If
    Next i

 

[Fluff]

Is this correct,

Does it work?

 

[welshgasman]

6 is for Friday?

I mentioned that you need to specify the starting day of the week?
Without the second parameter, it default to Sunday. That is why I said check the syntax

 

[awoohaw]

Here is excel formulas:

Book1
	A																																																				BA
1
2	Sat, 2023-02-25
3	Sun, 2023-02-26
4	Mon, 2023-02-27
5	Tue, 2023-02-28
6	Wed, 2023-03-01
7	Thu, 2023-03-02
8	Fri, 2023-03-03
9	Mon, 2023-03-27
10	Tue, 2023-03-28
11	Wed, 2023-03-29
12	Thu, 2023-03-30
13	Fri, 2023-03-31																																																				3
14	Sat, 2023-04-01
15	Sun, 2023-04-02
16	Wed, 2023-04-26
17	Thu, 2023-04-27
18	Fri, 2023-04-28																																																				3
19	Sat, 2023-04-29
20	Sun, 2023-04-30
21	Mon, 2023-05-01
22	Tue, 2023-05-02
Sheet1

Cell Formulas
Range	Formula
BA2:BA22	BA2	=IF(AND(WEEKDAY(A2)=6,DAY(WORKDAY.INTL(A2,1))<=3),3,"")