Solved

Show business day using formula

Posted on 2014-02-05
9
191 Views
Last Modified: 2014-02-05
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.

Thanks in advance
businessday.zip
0
Comment
Question by:leezac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 35

Expert Comment

by:mvidas
ID: 39836261
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 Comment

by:leezac
ID: 39836292
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


I changed your formula

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

Assisted Solution

by:mvidas
mvidas earned 500 total points
ID: 39836309
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:leezac
ID: 39836428
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
 
LVL 35

Expert Comment

by:mvidas
ID: 39836448
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)"

Open in new window

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

Open in new window

0
 

Author Comment

by:leezac
ID: 39836618
grhhh - should work but am getting  1004 run-time error, Application or object-defined error
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39836626
Where are you getting "Row" from?
0
 

Author Comment

by:leezac
ID: 39836664
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
 
LVL 35

Accepted Solution

by:
mvidas earned 500 total points
ID: 39836689
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)"

Open in new window

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

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question