Identify Fridays and long weekends

[rollingzep]

Hi,

In my Excel application, I need to set a value of a field, RWA based on another field, report date (RPT_DT) falling on Fridays or on long weekends.
If RPT_DT falls on Fridays, the value of RWA is 3 and if RPT_DT falls on long weekends, the RWA value is 4. For a midweek holiday it will be 2, and otherwise it will be a 1.

Can Excel's inbuilt function, NetworkDays be used and if so how in the above case?

TIA

 

[Skybluekid]

Your would be better using NETWORKDAYS.INTL. In this function you can specify holidays. Are you able to post some data?

 

[rollingzep]

Your would be better using NETWORKDAYS.INTL. In this function you can specify holidays. Are you able to post some data?

For Example, 12/30/2022 the RPT_DT was a Friday, but 01/31/2023 was a Tuesday. So, how to identify if the last working day of the month was a Friday or a weekday?

 

[Skybluekid]

If you just want to check if the week day is a Friday, then below should do the job

Book1.xlsx
	B	C
2	30/12/2022	TRUE
Sheet4

Cell Formulas
Range	Formula
C2	C2	=IF(WEEKDAY(B2,11)=5,TRUE,FALSE)

 

[rollingzep]

If you just want to check if the week day is a Friday, then below should do the job

Book1.xlsx
	B	C
2	30/12/2022	TRUE
Sheet4

Cell Formulas
Range	Formula
C2	C2	=IF(WEEKDAY(B2,11)=5,TRUE,FALSE)

I get an error on the highlighted code

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        [B]If (Weekday(ws.Range("B" & i, 11) = 5, True, False)) = True Then[/B]
            ws.Range("BG" & i).Value = DateDiff("D", ws.Range("B" & i), ws.Range("AY" & i)) - 1
        End If
    Next i

If it is "True", then value of BG should be one day less than DateDiff("D", ws.Range("B" & i), ws.Range("AY" & i))

How to do this?

 

[Skybluekid]

You dont need the [B} and [/B]

Try the below
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If (Weekday(ws.Range("B" & i).Value, 11) = 5 Then
ws.Range("BG" & i).Value = DateDiff("D", ws.Range("B" & i), ws.Range("AY" & i)) - 1
End If
Next i

 

[rollingzep]

You dont need the [B} and [/B]

Try the below
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If (Weekday(ws.Range("B" & i).Value, 11) = 5 Then
ws.Range("BG" & i).Value = DateDiff("D", ws.Range("B" & i), ws.Range("AY" & i)) - 1
End If
Next i

I have the date col format as 2023-01-31 00:00:00 and I get an error "invalid procedure call or argument on line 3

    'Check for Fridays
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If (Weekday(ws.Range("B" & i).Value, 11) = 5) Then
            ws.Range("BG" & i).Value = DateDiff("D", ws.Range("B" & i), ws.Range("AY" & i)) - 1
        End If
    Next i

 

[Skybluekid]

'Check for Fridays
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If (Weekday(INT(ws.Range("B" & i).Value), 11) = 5) Then
            ws.Range("BG" & i).Value = DateDiff("D", ws.Range("B" & i), ws.Range("AY" & i)) - 1
        End If
    Next i

 

[Fluff]

@Skybluekid in future please post code in code tags. How to Post Your VBA Code

 

[rollingzep]

'Check for Fridays
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If (Weekday(INT(ws.Range("B" & i).Value), 11) = 5) Then
            ws.Range("BG" & i).Value = DateDiff("D", ws.Range("B" & i), ws.Range("AY" & i)) - 1
        End If
    Next i

Now getting Type mismatch on this line

If (Weekday(INT(ws.Range("B" & i).Value), 11) = 5) Then

The col B is Text. And so is col AY. Should i change the format to date?