Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula
I have this formula that is working great, but I need to expand on it and I
have no idea how: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(J123="LOW SLIP",3,1),NWD))) The "NWD" at the end of the formula is a named range of dates (non-workdays). What I would like to add to this formula is to do with the last part (H123,IF(J123 ="LOW SLIP", etc.). This is what I need to incorporate in that: if the 3 days added to the date in H123 includes a weekend, then it would only add 1 day. But this would only be if J123 has "LOW SLIP" in it, otherwise it would add the 3 days. I hope someone can come to my aid because I am tired of updating this manually. Thank you. Connie |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula
I believe this is what you wanted:
=IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H23,3)-H233,IF(J123="LOW SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I have this formula that is working great, but I need to expand on it and I have no idea how: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(J123="LOW SLIP",3,1),NWD))) The "NWD" at the end of the formula is a named range of dates (non-workdays). What I would like to add to this formula is to do with the last part (H123,IF(J123 ="LOW SLIP", etc.). This is what I need to incorporate in that: if the 3 days added to the date in H123 includes a weekend, then it would only add 1 day. But this would only be if J123 has "LOW SLIP" in it, otherwise it would add the 3 days. I hope someone can come to my aid because I am tired of updating this manually. Thank you. Connie |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula
I really appreciate you tackling this one! However, this formula is giving
be #VALUE! I'm looking at it and trying figure it out, but can't! What do you think is giving this return? Connie "Luke M" wrote: I believe this is what you wanted: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H23,3)-H233,IF(J123="LOW SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I have this formula that is working great, but I need to expand on it and I have no idea how: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(J123="LOW SLIP",3,1),NWD))) The "NWD" at the end of the formula is a named range of dates (non-workdays). What I would like to add to this formula is to do with the last part (H123,IF(J123 ="LOW SLIP", etc.). This is what I need to incorporate in that: if the 3 days added to the date in H123 includes a weekend, then it would only add 1 day. But this would only be if J123 has "LOW SLIP" in it, otherwise it would add the 3 days. I hope someone can come to my aid because I am tired of updating this manually. Thank you. Connie |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula
Oops, I used H23, and you wanted H123
=IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H123,3)-H1233,IF(J123="LOW SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD))) That should fix it. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I really appreciate you tackling this one! However, this formula is giving be #VALUE! I'm looking at it and trying figure it out, but can't! What do you think is giving this return? Connie "Luke M" wrote: I believe this is what you wanted: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H23,3)-H233,IF(J123="LOW SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I have this formula that is working great, but I need to expand on it and I have no idea how: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(J123="LOW SLIP",3,1),NWD))) The "NWD" at the end of the formula is a named range of dates (non-workdays). What I would like to add to this formula is to do with the last part (H123,IF(J123 ="LOW SLIP", etc.). This is what I need to incorporate in that: if the 3 days added to the date in H123 includes a weekend, then it would only add 1 day. But this would only be if J123 has "LOW SLIP" in it, otherwise it would add the 3 days. I hope someone can come to my aid because I am tired of updating this manually. Thank you. Connie |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Difficult formula
Thank you. I get an answer now but the wrong date. Maybe this will help:
"LOW SLIP" is in J123. Apr-08-09 (formated as a date) is in H123 The answer in G123 where my formula is should be: Apr-13-09, but I am now getting Apr-09-09. The reason it should be Apr-13-09 is because of "LOW SLIP" in J123 it would normally have to add 3 days from H123, which would be Apr-11-09, which falls on a weekend, therefore it should go to Monday, Apr-13-09. The named range "NWD" is all non-workdays (statutory holidays and weekends). The way my formula is working now is it's adding 3 business days, and I am getting Apr-14-09. I know this is confusing, so if you wish to leave it alone, I won't blame you. I will work more with it Monday, if I get a chance. Thank you. Connie "Luke M" wrote: Oops, I used H23, and you wanted H123 =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H123,3)-H1233,IF(J123="LOW SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD))) That should fix it. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I really appreciate you tackling this one! However, this formula is giving be #VALUE! I'm looking at it and trying figure it out, but can't! What do you think is giving this return? Connie "Luke M" wrote: I believe this is what you wanted: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(WORKDAY(H23,3)-H233,IF(J123="LOW SLIP",1,3),IF(J123="LOW SLIP",3,1)),NWD))) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I have this formula that is working great, but I need to expand on it and I have no idea how: =IF(H123="","",IF(H123="Not Scheduled","TBA",WORKDAY(H123,IF(J123="LOW SLIP",3,1),NWD))) The "NWD" at the end of the formula is a named range of dates (non-workdays). What I would like to add to this formula is to do with the last part (H123,IF(J123 ="LOW SLIP", etc.). This is what I need to incorporate in that: if the 3 days added to the date in H123 includes a weekend, then it would only add 1 day. But this would only be if J123 has "LOW SLIP" in it, otherwise it would add the 3 days. I hope someone can come to my aid because I am tired of updating this manually. Thank you. Connie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficult formula question | Excel Worksheet Functions | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult Formula | Excel Worksheet Functions | |||
Difficult look up formula | Excel Worksheet Functions |