Formula expansion

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

Connie Martin

I need to add something to an existing formula and am not sure where or how
exactly to place it. My formula is:
=IF(H3="","",IF(H3="Not Scheduled","TBA",H3+2))

I have a named range called "NWD". That range is dates, formatted as dates.
It is actually all the non-work days of the year. The above formula is
giving me a date of two days beyond the date in H3, however, if the date that
is returned by the formula falls on a non-work day, it needs to go to the
next work day, so it needs to exclude any dates in the named ranged "NWD".
Thank you. Connie
 
Try this:

=IF(H3="","",IF(H3="Not Scheduled","TBA",WORKDAY(H3,2,NWD)))

The WORKDAY function requires that you have the Analysis ToolPak add-in
installed if you're using a version of Excel prior to Excel 2007.

You'll probably have to format the cell as Date.
 
Biff's answer is perfect but if you want to do it with VBA here is UDF

Function twoday(startday)
nextday = startday + 2
Do Until dateOK
mytest = Application.CountIf(Range("NWD"), nextday)
If mytest Then
nextday = nextday + 1
Else
dateOK = True
End If
Loop
twoday = nextday
End Function

best wishes
 
Thank you! I'm going to print this one and keep in case this would be a
better way to do it on another spreadsheet. For now, Biff's works well.
Thank you so much for responding. You folk make my life simpler and I wish I
had your expertise! Have a great weekend! Connie
 
One spreadsheet in my workbook is a little different from the rest. The
formula is:
=IF(H3="","",IF(H3="Not Scheduled","TBA",IF(J3="LOW SLIP",H3+3,H3+1)))
How do I incorporate this extra function to it? I tried, but the two ways I
tried didn't work. Connie
 
Try this:

=IF(H3="","",IF(H3="Not Scheduled","TBA",WORKDAY(H3,IF(J3="LOW
SLIP",3,1),NWD)))
 
Thank you, Biff. Thank you so much. That appears to be working great.
Double great weekend to you. Keep safe! Connie
 
Back
Top