Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
word wrap without row expansion? | Excel Discussion (Misc queries) | |||
Expansion of COUNT if Question | Excel Worksheet Functions | |||
Formula Expansion | Excel Discussion (Misc queries) | |||
Row Expansion | Excel Worksheet Functions | |||
number expansion and contraction | Excel Worksheet Functions |