Re: Previous month's last business day

**joeljoel** · 11-19-2010, 10:29 AM

Daddylonglegs - someone turned me on to this thread and this formula a possible solution to my problem. I am curious what the TODAY() part does in the formula? Is this formula only going to work on a rolling basis? I am looking for something that will work calculate last two working days each month. I already have a column (column G) that numbers the working days each month (for the whole decade) and a list of holidays in column M. Now in column H I need the formula to identify those last 2 days and return a value (let's say "1"). Will your formula work? Or will it need tweaking?

Thanks!!

**daddylonglegs** · 11-19-2010, 11:04 AM

Hello Joel, I moved your post to a new thread - you were referring to this thread here

What do you have in column G exactly, are they dates

**joeljoel** · 11-19-2010, 11:57 AM

Sorry, no they are numbers 1-20something or however many working days there are in the month. I do have the date in Column A if that's easier to work with. I just needed a count of the working days and then figured working with numbers might be easier. Perhaps not??

**daddylonglegs** · 11-19-2010, 12:10 PM

Does column A have every date or only the working days? If it has every date then try this formula for H2 copied down

=IF((MONTH(A2)<>MONTH(WORKDAY(A2,2,M$2:M$100)))*NETWORKDAYS(A2,A2),1,"")

or if you only list working days try

=IF(MONTH(A2)<>MONTH(WORKDAY(A2,2,M$2:M$100)),1,"")

assumes that M2:M100 has the holiday dates

**joeljoel** · 11-19-2010, 02:22 PM

Works like a charm. Thank you so much.

May I be so bold as to ask for some explanation/breakdown of the formula? I would like to complete my project, but I'd also like to learn so as not to take up more time with similar questions in the future. Teach me to fish, please!

So, if I wanted to work wioth a specific month, for example, December, to calculate the last 5 working days, can I use the same formula and substitute December in the place of the 2nd "MONTH" in the formula?

**daddylonglegs** · 11-19-2010, 05:25 PM

Which formula did you use?

**joeljoel** · 11-19-2010, 05:44 PM

The first one - for every day written down

**daddylonglegs** · 11-19-2010, 05:53 PM

OK, there are several ways to do this I think, you could possibly use the numbers in column G but I reckon this is a little shorter.

For either the last working day in the month or the second last if you add 2 working days you will get a date in the following month (that wouldn't be true for earlier dates) so if you test the month of A2 against the month of WORKDAY(A2,2,holidays) then when they don't match you know that you have one of the last 2 workdays, i.e.

=IF(MONTH(A2)<>MONTH(WORKDAY(A2,2,M$2:M$100)),1,"")

....but if you have all dates listed then that will also put a 1 against any weekend or holiday date that comes after the second last workday......so you also have to test that A2 itself is a workday. You can do that by using NETWORKDAYS(A2,A2), if A2 is a workday that returns 1 if not it returns zero.

In fact you need to modify the formula I suggested, the holiday range should also go in the NETWORKDAYS function like this

=IF((MONTH(A2)<>MONTH(WORKDAY(A2,2,M$2:M$100)))*NETWORKDAYS(A2,A2,M$2:M$100),1,"")