If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
WORKDAY Function
I've read, read, and read about the WORKDAY function (Excel Help and online)
and cannot find a solution as to why my formula is giving me #NUM!. I read the reasons why, but cannot find any errors. All columns with dates are formated to 'date'. Analysis Toolpak is checked in Tools/Add-ins. The formula in I3 is this: =WORKDAY(J3,1,NWD) • J3 is the date 16-Feb-10, formatted as date (format cells, etc.) • J3's date comes from a VLOOKUP function, from a column that is formatted as a date • NWD (meaning "non-workdays") is a named range in another worksheet of the same workbook, and is formatted as a date (format cells, etc.). • NWD was inserted into the formula by Insert/Name/Paste • With this formula in I3 it should give 17-Feb-10. Very simple, this one. But it gives me #NUM!. Can anyone shed some light on this? It's getting very frustrating! I would be so very, very grateful if you could. Thank you. Connie |
#2
|
|||
|
|||
WORKDAY Function
Agreed, very confusing. From my tests, is appears WORKDAY is able to handle a
start date formatted as text, so that can't be the problem. You clearly have an legitimate number in the days arguement... The only thing I was able to do that created a #NUM error in your scenario was to have a negative number in the NWD range. This is a slim chance, but possibly? Further debugging: Does =WORKDAY(J3,1) work? Instead of using the named range, manually input an address, starting with a smaller size, and gradually increasing until the error is generated. Sorry I wasn't able to find a easy solution. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I've read, read, and read about the WORKDAY function (Excel Help and online) and cannot find a solution as to why my formula is giving me #NUM!. I read the reasons why, but cannot find any errors. All columns with dates are formated to 'date'. Analysis Toolpak is checked in Tools/Add-ins. The formula in I3 is this: =WORKDAY(J3,1,NWD) • J3 is the date 16-Feb-10, formatted as date (format cells, etc.) • J3's date comes from a VLOOKUP function, from a column that is formatted as a date • NWD (meaning "non-workdays") is a named range in another worksheet of the same workbook, and is formatted as a date (format cells, etc.). • NWD was inserted into the formula by Insert/Name/Paste • With this formula in I3 it should give 17-Feb-10. Very simple, this one. But it gives me #NUM!. Can anyone shed some light on this? It's getting very frustrating! I would be so very, very grateful if you could. Thank you. Connie |
#3
|
|||
|
|||
WORKDAY Function
Luke, thank you so much for replying. Your debug test worked. So the
problem was in the named range. I did the next thing you said, and put in the range A1:A10 in the formula instead of NWD, the named range, and formula worked! So, I looked at the named range, as it had to be the problem. When I named the range, I selected the entire column instead of just the cells with dates. So, I deleted the named range, selected just the dates A1:A10 and renamed it NWD, and the formula works! Marvelous! Oh! I'm so happy! Now I can go places!! Thank you a million times! Connie "Luke M" wrote: Agreed, very confusing. From my tests, is appears WORKDAY is able to handle a start date formatted as text, so that can't be the problem. You clearly have an legitimate number in the days arguement... The only thing I was able to do that created a #NUM error in your scenario was to have a negative number in the NWD range. This is a slim chance, but possibly? Further debugging: Does =WORKDAY(J3,1) work? Instead of using the named range, manually input an address, starting with a smaller size, and gradually increasing until the error is generated. Sorry I wasn't able to find a easy solution. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I've read, read, and read about the WORKDAY function (Excel Help and online) and cannot find a solution as to why my formula is giving me #NUM!. I read the reasons why, but cannot find any errors. All columns with dates are formated to 'date'. Analysis Toolpak is checked in Tools/Add-ins. The formula in I3 is this: =WORKDAY(J3,1,NWD) • J3 is the date 16-Feb-10, formatted as date (format cells, etc.) • J3's date comes from a VLOOKUP function, from a column that is formatted as a date • NWD (meaning "non-workdays") is a named range in another worksheet of the same workbook, and is formatted as a date (format cells, etc.). • NWD was inserted into the formula by Insert/Name/Paste • With this formula in I3 it should give 17-Feb-10. Very simple, this one. But it gives me #NUM!. Can anyone shed some light on this? It's getting very frustrating! I would be so very, very grateful if you could. Thank you. Connie |
#4
|
|||
|
|||
WORKDAY Function
Hurrah, glad it helped.
Furhter study: I thought at first the problem with having the whole column called out was all the blank cells, but it's not. It simply can't handle calling out the entire column. Ironically, if you try this: =WORKDAY(A1,1,B1:B65535) you're ok, but if you try to include that last cell: =WORKDAY(A1,1,B1:B65536) XL will "help" you out can change the final address to simply B:B, and then crash the formula! -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: Luke, thank you so much for replying. Your debug test worked. So the problem was in the named range. I did the next thing you said, and put in the range A1:A10 in the formula instead of NWD, the named range, and formula worked! So, I looked at the named range, as it had to be the problem. When I named the range, I selected the entire column instead of just the cells with dates. So, I deleted the named range, selected just the dates A1:A10 and renamed it NWD, and the formula works! Marvelous! Oh! I'm so happy! Now I can go places!! Thank you a million times! Connie "Luke M" wrote: Agreed, very confusing. From my tests, is appears WORKDAY is able to handle a start date formatted as text, so that can't be the problem. You clearly have an legitimate number in the days arguement... The only thing I was able to do that created a #NUM error in your scenario was to have a negative number in the NWD range. This is a slim chance, but possibly? Further debugging: Does =WORKDAY(J3,1) work? Instead of using the named range, manually input an address, starting with a smaller size, and gradually increasing until the error is generated. Sorry I wasn't able to find a easy solution. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Connie Martin" wrote: I've read, read, and read about the WORKDAY function (Excel Help and online) and cannot find a solution as to why my formula is giving me #NUM!. I read the reasons why, but cannot find any errors. All columns with dates are formated to 'date'. Analysis Toolpak is checked in Tools/Add-ins. The formula in I3 is this: =WORKDAY(J3,1,NWD) • J3 is the date 16-Feb-10, formatted as date (format cells, etc.) • J3's date comes from a VLOOKUP function, from a column that is formatted as a date • NWD (meaning "non-workdays") is a named range in another worksheet of the same workbook, and is formatted as a date (format cells, etc.). • NWD was inserted into the formula by Insert/Name/Paste • With this formula in I3 it should give 17-Feb-10. Very simple, this one. But it gives me #NUM!. Can anyone shed some light on this? It's getting very frustrating! I would be so very, very grateful if you could. Thank you. Connie |
Thread Tools | |
Display Modes | |
|
|