Formula expansion

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
 
T

T. Valko

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.
 
B

Bernard Liengme

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
 
C

Connie Martin

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
 
C

Connie Martin

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
 
T

T. Valko

Try this:

=IF(H3="","",IF(H3="Not Scheduled","TBA",WORKDAY(H3,IF(J3="LOW
SLIP",3,1),NWD)))
 
C

Connie Martin

Thank you, Biff. Thank you so much. That appears to be working great.
Double great weekend to you. Keep safe! Connie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top