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

 

[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,"")

This does not take holidays into consideration. What will you do when Friday is within 3 days of the last day of the month and it is a Holiday?
Does the Thursday then mean that BA becomes a 3? Holiday table can easily be incorporated into the WORKDAY.INTL function.

 

[Fluff]

I mentioned that you need to specify the starting day of the week?

No you don't, it will simply default to Sunday & therefore Friday is 6

 

[rollingzep]

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

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

I have just the date in Col B and no second parameter. Or I am not getting you.

 

[Fluff]

Have you tried what I suggested?

 

[awoohaw]

@rollingzep, did you look at Posts 10 & 11? I'm not sure if you're seeking a VBA or Excel Worksheet Formula solution.

 

[Fluff]

Judging by th fact the OP has posted code, I suspect a VBA solution.

 

[awoohaw]

@rollingzep, did you look at Posts 10 & 11? I'm not sure if youre seeking a VBA or Excel Worksheet Formula solution.

Judging by th fact the OP has posted code, I suspect a VBA solution.

I did not read that as part of the intial requirements. Sometimes people overthink things (I am a perpetrator in this regard as well).
But, as to your solution, how does your formula check to see that it is the last day of the month? Or is that something else I missed?

subsequent edit: Last business day of the month.