Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workday Formula?
I've been trying to develop a production schedule in Excel. I'd like to put
an end date and have Excel calculate each activities' start date based on the number of days needed. I can do a simple subtraction formula (end date-#of days) however that does not take into account weekends and holidays. I tried the Workday function but that didn't work. Suggestions are most appreciated. Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workday Formula?
What didn't work? Did you get errors or was the result incorrect?
It's part of the ATP (Analysis ToolPak) that comes with Excel/Office but it won't install using the default installation and if it's not installed you will get a name error. If so just go to toolsadd-ins and select it, then plop in the installation CD when prompted -- Regards, Peo Sjoblom "Fernbars" wrote in message ... I've been trying to develop a production schedule in Excel. I'd like to put an end date and have Excel calculate each activities' start date based on the number of days needed. I can do a simple subtraction formula (end date-#of days) however that does not take into account weekends and holidays. I tried the Workday function but that didn't work. Suggestions are most appreciated. Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workday Formula?
If I use the 'Workday' function, I get an incorrect result. If I use
'NetWorkday', I get an error. I have the end date at the bottom of the spreadsheet and rows with number of days for completion of the project. When I use the formula 'Workday(end date - #of days)', I get #NUM error. If I use the formula 'Workday(end date,#of days)', I get an incorrect result. Many thanks! "Peo Sjoblom" wrote: What didn't work? Did you get errors or was the result incorrect? It's part of the ATP (Analysis ToolPak) that comes with Excel/Office but it won't install using the default installation and if it's not installed you will get a name error. If so just go to toolsadd-ins and select it, then plop in the installation CD when prompted -- Regards, Peo Sjoblom "Fernbars" wrote in message ... I've been trying to develop a production schedule in Excel. I'd like to put an end date and have Excel calculate each activities' start date based on the number of days needed. I can do a simple subtraction formula (end date-#of days) however that does not take into account weekends and holidays. I tried the Workday function but that didn't work. Suggestions are most appreciated. Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workday Formula?
Is it one day off? Both WORKDAY and NETWORKDAYS include the dates meaning
that if you would use =WORKDAY(TODAY(),10) then today's date would be included in the count, some people consider that incorrect. Also the NUM error derives from an impossible calculation for example =WORKDAY(TODAY()-100000) You might also want to check under toolsoptionstransition and make sure you don't have anything checked there since it will screw up the dates and make any WORKDAY formula with -10 return a #NUM! error -- Regards, Peo Sjoblom "Fernbars" wrote in message ... If I use the 'Workday' function, I get an incorrect result. If I use 'NetWorkday', I get an error. I have the end date at the bottom of the spreadsheet and rows with number of days for completion of the project. When I use the formula 'Workday(end date - #of days)', I get #NUM error. If I use the formula 'Workday(end date,#of days)', I get an incorrect result. Many thanks! "Peo Sjoblom" wrote: What didn't work? Did you get errors or was the result incorrect? It's part of the ATP (Analysis ToolPak) that comes with Excel/Office but it won't install using the default installation and if it's not installed you will get a name error. If so just go to toolsadd-ins and select it, then plop in the installation CD when prompted -- Regards, Peo Sjoblom "Fernbars" wrote in message ... I've been trying to develop a production schedule in Excel. I'd like to put an end date and have Excel calculate each activities' start date based on the number of days needed. I can do a simple subtraction formula (end date-#of days) however that does not take into account weekends and holidays. I tried the Workday function but that didn't work. Suggestions are most appreciated. Thank you! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workday Formula?
I think you just have a minor syntax / typo issue.
But, to clear things up before we go any further: WORKDAY works out what date is X days after (minus = before) a given start date, ignoring wekends and optional holidays NETWORKDAYS does the opposite - works out how many days are between two given dates, excluding weekends and hols. So, it sounds like WORKDAY is what you need, but you want to go backwards. That's fine, but your arguments still need to be separated by a comma, so you want something like: =WORKDAY(EndDate, -NumDays, [Hols]) NOTE the comma after EndDate - you can't do "EndDate-NumDays" or there are too few arguments (or rather the first holiday in your list would get included as NumDays, and may well be out of range) (apologies if your missed comma was a typo in the forum, rather than in your actual spreadsheet formula) As mentioned above, watch out for whether you include or exclude the end date and modify accordingly (this depends on your business model, there's no 'right' answer) As an aside - make sure you use a named range to point at your list of holidays so you can easily modify to keep up with changes as you add for future years. -- Adam Vero MCP, MOS Master, MLSS, CWNA http://veroblog.wordpress.com http://www.meteorit.co.uk "Fernbars" wrote: I've been trying to develop a production schedule in Excel. I'd like to put an end date and have Excel calculate each activities' start date based on the number of days needed. I can do a simple subtraction formula (end date-#of days) however that does not take into account weekends and holidays. I tried the Workday function but that didn't work. Suggestions are most appreciated. Thank you! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workday Formula?
Great post but I am using the following and getting an error.
=WORKDAY(H19, -10, [hols]) and I have created range HOLS which are all the cells with the Holidays. Fomula works fine without HOLS but when I add it I get the error. Help Please. -- Brian "AdamV" wrote: I think you just have a minor syntax / typo issue. But, to clear things up before we go any further: WORKDAY works out what date is X days after (minus = before) a given start date, ignoring wekends and optional holidays NETWORKDAYS does the opposite - works out how many days are between two given dates, excluding weekends and hols. So, it sounds like WORKDAY is what you need, but you want to go backwards. That's fine, but your arguments still need to be separated by a comma, so you want something like: =WORKDAY(EndDate, -NumDays, [Hols]) NOTE the comma after EndDate - you can't do "EndDate-NumDays" or there are too few arguments (or rather the first holiday in your list would get included as NumDays, and may well be out of range) (apologies if your missed comma was a typo in the forum, rather than in your actual spreadsheet formula) As mentioned above, watch out for whether you include or exclude the end date and modify accordingly (this depends on your business model, there's no 'right' answer) As an aside - make sure you use a named range to point at your list of holidays so you can easily modify to keep up with changes as you add for future years. -- Adam Vero MCP, MOS Master, MLSS, CWNA http://veroblog.wordpress.com http://www.meteorit.co.uk "Fernbars" wrote: I've been trying to develop a production schedule in Excel. I'd like to put an end date and have Excel calculate each activities' start date based on the number of days needed. I can do a simple subtraction formula (end date-#of days) however that does not take into account weekends and holidays. I tried the Workday function but that didn't work. Suggestions are most appreciated. Thank you! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workday Formula?
Hi
You don't need the square brackets around hols. =WORKDAY(H19, -10, hols) -- Regards Roger Govier "bdehning" wrote in message ... Great post but I am using the following and getting an error. =WORKDAY(H19, -10, [hols]) and I have created range HOLS which are all the cells with the Holidays. Fomula works fine without HOLS but when I add it I get the error. Help Please. -- Brian "AdamV" wrote: I think you just have a minor syntax / typo issue. But, to clear things up before we go any further: WORKDAY works out what date is X days after (minus = before) a given start date, ignoring wekends and optional holidays NETWORKDAYS does the opposite - works out how many days are between two given dates, excluding weekends and hols. So, it sounds like WORKDAY is what you need, but you want to go backwards. That's fine, but your arguments still need to be separated by a comma, so you want something like: =WORKDAY(EndDate, -NumDays, [Hols]) NOTE the comma after EndDate - you can't do "EndDate-NumDays" or there are too few arguments (or rather the first holiday in your list would get included as NumDays, and may well be out of range) (apologies if your missed comma was a typo in the forum, rather than in your actual spreadsheet formula) As mentioned above, watch out for whether you include or exclude the end date and modify accordingly (this depends on your business model, there's no 'right' answer) As an aside - make sure you use a named range to point at your list of holidays so you can easily modify to keep up with changes as you add for future years. -- Adam Vero MCP, MOS Master, MLSS, CWNA http://veroblog.wordpress.com http://www.meteorit.co.uk "Fernbars" wrote: I've been trying to develop a production schedule in Excel. I'd like to put an end date and have Excel calculate each activities' start date based on the number of days needed. I can do a simple subtraction formula (end date-#of days) however that does not take into account weekends and holidays. I tried the Workday function but that didn't work. Suggestions are most appreciated. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workday function linked in an argument of If formula. | Excel Worksheet Functions | |||
WORKDAY() and probably more | New Users to Excel | |||
Workday Help | Excel Discussion (Misc queries) | |||
WORKDAY formula in excel | Excel Worksheet Functions | |||
=SUM((B7+2),IF($D7>0.Workday,($D7+E$8,Holidays!$C4:$C11$),'''')) | Excel Worksheet Functions |