• Status: Solved
• Priority: Medium
• Security: Public
• Views: 199

# Show business day using formula

I need a formula to copy down date in column A based on values in column B.  The date needs to be the last business date.  If today is Tuesday and  Monday is a holiday then it would show Friday's date.  If today is Monday then would show Friday's date.  If a weekday and is a Tuesday and Monday is not a Holiday then would show Monday's date.

I have added a file where I have a formula and a tab for Holidays.

0
leezac
• 5
• 4
2 Solutions

Commented:
leezac,

This should work for you. The -1 means "one day before", and the range specifies holidays to skip. Put this in A2 and copy down.

=WORKDAY(B2,-1,Holidays!\$A\$2:\$A\$18)

Matt
0

Author Commented:
thanks but that does not work

I need to be based off of today's date and

I am thinking this might work.    =WORKDAY(NOW(),-1) without the table

=WORKDAY(TODAY(),-1,Holidays!\$A\$2:\$A\$9)  but not sure if will work for weekends and Holidays
0

Commented:
I thought you said you wanted it based on a value in column B; I realize now you meant only when there is a value in B.

If you always want the previous working day, then your formula at the bottom will be fine.
=WORKDAY(TODAY(),-1,Holidays!\$A\$2:\$A\$9)
0

Author Commented:
ok -

now trying to copy down and it does not like the Range

If ActiveSheet.Name = "Variances" Then

Range("A3:A" & Row - 1).Formula = "=WORKDAY(TODAY(),-1,Holidays!R2C1:R9C1)"
End If
End Sub
0

Commented:
If you want to use the R1C1 notation, you'll have to use .FormulaR1C1
``````Sheets("Variances").Range("A3:A" & Row - 1).FormulaR1C1 = "=WORKDAY(TODAY(),-1,Holidays!R2C1:R9C1)"
``````
Or if you wanted to just use the address directly, then you'd use formula (fine in your situation, since you're not using any relative references)
``````Sheets("Variances").Range("A3:A" & Row - 1).Formula = "=WORKDAY(TODAY(),-1,Holidays!\$A\$2:\$A\$9)"
``````
0

Author Commented:
grhhh - should work but am getting  1004 run-time error, Application or object-defined error
0

Commented:
Where are you getting "Row" from?
0

Author Commented:
Well - it was from another formulas I was using in another procedure, but I took it out and still or an error Application or Object defined error

Sheets("Variances").Range("A3:A").FormulaR1C1 = "=WORKDAY(TODAY(),-1,Holidays!R2C1:R9C1)"

How would it know to copy down to last row of used range of B3???
0

Commented:
I figured it might be a leftover variable, since Row is usually a reserved word.  Using the same logic, we can store the last used row number in B in a variable and use it as you had it:
``````Dim lastRow As Long
lastRow = Sheets("Variances").Range("B" & Rows.Count).End(xlUp).Row
Sheets("Variances").Range("A3:A" & lastRow).FormulaR1C1 = "=WORKDAY(TODAY(),-1,Holidays!R2C1:R9C1)"
``````
There are many other ways to accomplish the same but this method is an easy one to follow if you're still learning macros. This is the same idea as going to the last cell in column B, pressing control-up, and looking to see what row you end up in. The only way this would be troublesome is if the last used row is row 1 or 2. If this might be the case, you can test for it and only use the formula when theres something in column B:
``````Dim lastRow As Long
lastRow = Sheets("Variances").Range("B" & Rows.Count).End(xlUp).Row
If lastRow >= 3 Then
Sheets("Variances").Range("A3:A" & lastRow).FormulaR1C1 = "=WORKDAY(TODAY(),-1,Holidays!R2C1:R9C1)"
End If
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.