Formula expansion
 

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

Formula expansion
 

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
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Formula expansion
 

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Connie Martin" wrote in message
...
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

Formula expansion
 

Thank you so much. This works wonderfully! Have a great weekend! Connie

"T. Valko" wrote:

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
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Formula expansion
 

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

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Connie Martin" wrote in message
...
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

Formula expansion
 

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. Valko" wrote:

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
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Formula expansion
 

Try this:

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


--
Biff
Microsoft Excel MVP


"Connie Martin" wrote:

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. Valko" wrote:

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
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Formula expansion
 

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

"T. Valko" wrote:

Try this:

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


--
Biff
Microsoft Excel MVP


"Connie Martin" wrote:

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. Valko" wrote:

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
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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

Formula expansion
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Connie Martin" wrote:

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

"T. Valko" wrote:

Try this:

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


--
Biff
Microsoft Excel MVP


"Connie Martin" wrote:

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. Valko" wrote:

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
Microsoft Excel MVP


"Connie Martin" wrote in message
...
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