If cells b5, d5, and/or g5 are empty (one or all), run a formula to calculate a due date

E

eabaker64

New Member
Joined
Jul 1, 2024
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Hi. I need a formula in B3 to look at cells B5, D5 and G5. If any of those cells are blank, a formula is run which calculates a date that is 15 workdays after a date showing in cell B2. If cells B5, D5 and G5 all contain data, the formula does not run and instead returns a value of N/A in cell B3. I have this formula but if any of the B5, D5, or G5 cells have data, it returns N/A. I don't want it to return N/A unless all three cells contain data. Any ideas?

=IF(B5<>"","N/A",IF(D5<>"","N/A",IF(G5<>"","N/A",IF(B2<>"",WORKDAY(B2,15),""))))
 
Sort by date Sort by votes
dreid1011 said:
Okay, first I added a line to the code, so try this one:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case Else
                GoTo ExitNow
       
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub

And second, in the Immediate window, type "Application.EnableEvents = True" (without quotes) and press enter.
View attachment 114167

If your Immediate window is not open, either press Ctrl + G or go to View, and select Immediate Window.

After entering the line above, try to enter dates into the cells.
Click to expand...
I tried it and it populates dates in the cells. YAY!!!!! Thank you so much. It would have taken me months to figure that out...and even then I don't think I would have found everything that you have. Again, thank you so much. You have saved me and my tracking worksheet so much time.
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
You're quite welcome, and happy to help. If you need it adjusted to run on col G, just let me know.
 
Upvote 0
dreid1011 said:
You're quite welcome, and happy to help. If you need it adjusted to run on col G, just let me know.
Click to expand...
Okay, I'm back. I don't need column G, and I think if I decided down the road that I did need it, I could follow your code and attempt to write it. I have come upon one glitch. If I don't enter a date in B9 or D9 after a date is populated in B6, it doesn't calculate the follow up due date in B7. It does if I enter a date in either B9 or D9, but sometimes we don't get everything resolved the first go around. Is there a line of code that could go in that would calculate the due date in B7 if nothing is entered in B9 and D9? You're going to be so sick of me. :(
 
Upvote 0
eabaker64 said:
Okay, I'm back. I don't need column G, and I think if I decided down the road that I did need it, I could follow your code and attempt to write it. I have come upon one glitch. If I don't enter a date in B9 or D9 after a date is populated in B6, it doesn't calculate the follow up due date in B7. It does if I enter a date in either B9 or D9, but sometimes we don't get everything resolved the first go around. Is there a line of code that could go in that would calculate the due date in B7 if nothing is entered in B9 and D9? You're going to be so sick of me. :(
Click to expand...
Okay, try this:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B6"), Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$6"
                If Range("B7") = "" Then
                    Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                End If
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("B7") = "" Then
                            Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        End If
                        GoTo ExitNow
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("D7") = "" Then
                            Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        End If
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case Else
                GoTo ExitNow
        
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub
 
Upvote 0
dreid1011 said:
Okay, try this:

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B6"), Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$6"
                If Range("B7") = "" Then
                    Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                End If
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("B7") = "" Then
                            Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        End If
                        GoTo ExitNow
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("D7") = "" Then
                            Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        End If
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                End If
            Case Else
                GoTo ExitNow
       
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub
Click to expand...
Okay, it entered a date in there if nothing is entered in B9 or D9. Is there a way to clear that date if something is entered in both B9 and D9? Everything else seems to be working perfect. I think that is the last little tweek that it would need.
 
Upvote 0
eabaker64 said:
Okay, it entered a date in there if nothing is entered in B9 or D9. Is there a way to clear that date if something is entered in both B9 and D9? Everything else seems to be working perfect. I think that is the last little tweek that it would need.
Click to expand...
Okay:

And what about D7?

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B6"), Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$6"
                If Range("B7") = "" Then
                    Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                End If
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("B7") = "" Then
                            Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        End If
                        GoTo ExitNow
                    End If
                Else
                    If Range("B9") <> "" Then
                        Range("B7") = ""
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("D7") = "" Then
                            Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        End If
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                Else
                    If Range("D9") <> "" Then
                        Range("B7") = ""
                    End If
                End If
            Case Else
                GoTo ExitNow
       
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub
 
Upvote 0
dreid1011 said:
Okay:

And what about D7?

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cont, dateB, dateD
Dim msgB As String, msgD As String
Dim tRng As Range
Set tRng = Union(Range("B6"), Range("B8"), Range("B9"), Range("D6"), Range("D9"))

Application.EnableEvents = False

msgB = "Enter D9 date?"
msgD = "Enter B9 date?"

On Error GoTo ExitNow
If Not Intersect(Target, tRng) Is Nothing And Target.Value <> "" And Target.Count = 1 Then
        Select Case Target.Address
            Case "$B$6"
                If Range("B7") = "" Then
                    Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                End If
            Case "$B$8"
                Range("D5") = WorksheetFunction.WorkDay(Range("B8"), 10)
            Case "$B$9"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("B7") = "" Then
                            Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        End If
                        GoTo ExitNow
                    End If
                Else
                    If Range("B9") <> "" Then
                        Range("B7") = ""
                    End If
                End If
            Case "$D$6"
                If Range("D9") = "" Then
                    cont = MsgBox(msgB, vbYesNo)
                    If cont = vbYes Then
                        dateB = Application.InputBox("Enter D9 Date")
                        Range("D9") = dateB
                    Else
                        If Range("D7") = "" Then
                            Range("D7") = WorksheetFunction.WorkDay(Range("D6"), 10)
                        End If
                        GoTo ExitNow
                    End If
                End If
            Case "$D$9"
                If Range("B9") = "" Then
                    cont = MsgBox(msgD, vbYesNo)
                    If cont = vbYes Then
                        dateD = Application.InputBox("Enter B9 Date")
                        Range("B9") = dateD
                    Else
                        Range("B7") = WorksheetFunction.WorkDay(Range("B6"), 15)
                        GoTo ExitNow
                    End If
                Else
                    If Range("D9") <> "" Then
                        Range("B7") = ""
                    End If
                End If
            Case Else
                GoTo ExitNow
      
        End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub
Click to expand...
Good morning. I tried that code. It still leaves a date in B7 when B9 and D9 have dates after the date is entered in B6 (the first go around). But, if no dates are entered in B9 & D9 until a date is completed in D6, then it removes the date in B7. Unfortunately, I need the date to remain in B7 if B9/D9 are not populated until the 2nd response is received (B6). And thank you for thinking of D7 because if dates are entered in B9/D9 after a date is entered in D6, I would like D7 to remain blank. I hope I explained that okay. Thank you.
 
Upvote 0
eabaker64 said:
Good morning. I tried that code. It still leaves a date in B7 when B9 and D9 have dates after the date is entered in B6 (the first go around). But, if no dates are entered in B9 & D9 until a date is completed in D6, then it removes the date in B7. Unfortunately, I need the date to remain in B7 if B9/D9 are not populated until the 2nd response is received (B6). And thank you for thinking of D7 because if dates are entered in B9/D9 after a date is entered in D6, I would like D7 to remain blank. I hope I explained that okay. Thank you.
Click to expand...
Not quite following. "2nd Response" is col D, but you're indicating "2nd Response Received" is B6.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Lookup formula not working if pointed to cells with formulas
Replies
4
Views
327
Fluff
Fluff
F
  • Solved
Put a mark on rows that have the same 10 digits but different last one digit
Replies
2
Views
187
feni1388
F
A
  • Solved
Excel formula does not return expected result when encountering a cell that includes text in a formula reference
2
Replies
11
Views
1K
Joe4
Joe4
J
  • Solved
Long IF Formula Calculation Trouble
Replies
6
Views
262
Alex Blakenburg
A
S
  • Question Question
Using IFS to calculate a task due by date but needing to remove weekends
Replies
3
Views
444
awoohaw
awoohaw

Forum statistics

Threads
1,223,903
Messages
6,175,286
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top