Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
Hi,
I have a holiday list set up, and a formula that works out the projected completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the formula so that if a day within the holiday list is predicted it skips to a day after (which is not a holiday), and still gives an accurate date/time e.g Predicted time to complete is say 4 hours start date/time: 28/12/2007 16:40 Finish time should be: 31/12/07 11:40 (The formula takes is to take into account working day (9-5 mon-fri) Please help! Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
Show us your formula, and we might be able to help.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... Hi, I have a holiday list set up, and a formula that works out the projected completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the formula so that if a day within the holiday list is predicted it skips to a day after (which is not a holiday), and still gives an accurate date/time e.g Predicted time to complete is say 4 hours start date/time: 28/12/2007 16:40 Finish time should be: 31/12/07 11:40 (The formula takes is to take into account working day (9-5 mon-fri) Please help! Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
Thu, 27 Dec 2007 01:35:00 -0800 from DaveAsh
: I have a holiday list set up, and a formula that works out the projected completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the formula so that if a day within the holiday list is predicted it skips to a day after (which is not a holiday), and still gives an accurate date/time See the WORKDAY() function. I believe you must have Analysis Toolpak installed. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
=LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"})
J38 is the job priority (1-5) 1-Completion should be within 4 working hours 2-" " " " " 1 day 3-" " " " " 2 days 4-" " " " " 7 days 5-" " " " " 14 days E38 is the job start date I know the formula I am using is not the best but can i add to it a workday and IF function? Or is there a better way to do this? Thanks. "Bob Phillips" wrote: Show us your formula, and we might be able to help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... Hi, I have a holiday list set up, and a formula that works out the projected completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the formula so that if a day within the holiday list is predicted it skips to a day after (which is not a holiday), and still gives an accurate date/time e.g Predicted time to complete is say 4 hours start date/time: 28/12/2007 16:40 Finish time should be: 31/12/07 11:40 (The formula takes is to take into account working day (9-5 mon-fri) Please help! Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
Didn't I give you this before, where I used the WORKDAY function embedded in
part of what you have? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... =LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"}) J38 is the job priority (1-5) 1-Completion should be within 4 working hours 2-" " " " " 1 day 3-" " " " " 2 days 4-" " " " " 7 days 5-" " " " " 14 days E38 is the job start date I know the formula I am using is not the best but can i add to it a workday and IF function? Or is there a better way to do this? Thanks. "Bob Phillips" wrote: Show us your formula, and we might be able to help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... Hi, I have a holiday list set up, and a formula that works out the projected completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the formula so that if a day within the holiday list is predicted it skips to a day after (which is not a holiday), and still gives an accurate date/time e.g Predicted time to complete is say 4 hours start date/time: 28/12/2007 16:40 Finish time should be: 31/12/07 11:40 (The formula takes is to take into account working day (9-5 mon-fri) Please help! Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
Yeah, sorry. I now have the following:
=WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2),AA1:AA34)+MO D(E38,1)+VLOOKUP(J38,$D$1:$F$5,3)+(MOD(E38+VLOOKUP (J38,$D$1:$F$5,3),1)TIME(17,0,0))*2/3 (AA1:AA34 are a list of weekends) The only problem that i have with this though is that when the priority is 1 (4 hours projected time) and the start date/time is after 1pm on a Friday, the formula still gives weekends as a projected finish date. How can i adjust the formula to take this into account? "Bob Phillips" wrote: Didn't I give you this before, where I used the WORKDAY function embedded in part of what you have? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... =LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"}) J38 is the job priority (1-5) 1-Completion should be within 4 working hours 2-" " " " " 1 day 3-" " " " " 2 days 4-" " " " " 7 days 5-" " " " " 14 days E38 is the job start date I know the formula I am using is not the best but can i add to it a workday and IF function? Or is there a better way to do this? Thanks. "Bob Phillips" wrote: Show us your formula, and we might be able to help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... Hi, I have a holiday list set up, and a formula that works out the projected completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the formula so that if a day within the holiday list is predicted it skips to a day after (which is not a holiday), and still gives an accurate date/time e.g Predicted time to complete is say 4 hours start date/time: 28/12/2007 16:40 Finish time should be: 31/12/07 11:40 (The formula takes is to take into account working day (9-5 mon-fri) Please help! Thanks. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
WORKDAY shouldn't need a list of weekends. The second (optional) parameter
is a list of holidays. Weekends get omitted anyway. -- David Biddulph "DaveAsh" wrote in message ... Yeah, sorry. I now have the following: =WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2),AA1:AA34)+MO D(E38,1)+VLOOKUP(J38,$D$1:$F$5,3)+(MOD(E38+VLOOKUP (J38,$D$1:$F$5,3),1)TIME(17,0,0))*2/3 (AA1:AA34 are a list of weekends) .... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
I know it should omit weekends, but for some reason the formula i am using
does not even with the add-in. Maybe there is an error within the formula that is stopping this? "David Biddulph" wrote: WORKDAY shouldn't need a list of weekends. The second (optional) parameter is a list of holidays. Weekends get omitted anyway. -- David Biddulph "DaveAsh" wrote in message ... Yeah, sorry. I now have the following: =WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2),AA1:AA34)+MO D(E38,1)+VLOOKUP(J38,$D$1:$F$5,3)+(MOD(E38+VLOOKUP (J38,$D$1:$F$5,3),1)TIME(17,0,0))*2/3 (AA1:AA34 are a list of weekends) .... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
You've added lots of extra terms after the WORKDAY function. Have you
checked that WORKDAY is doing the wrong thing in isolation? It's always a good idea to break a long formula into manageable chunks if you need to debug it. I don't know quite how your formula is intended to work, but I would have thought that most of the terms would need to be inside a WORKDAY function to get the result on the right day. If you are adding an extra term after your first WORKDAY function and that extra term might take it from one day to another, then you'll probably need another WORKDAY function to allow that to step over the weekend. If your extra terms might give a number of whole days + a time of day for the finishing point, then perhaps =WORKDAY(WORKDAY(... first addition ...),INT(extra term))+MOD(extra term,1) ? -- David Biddulph "DaveAsh" wrote in message ... I know it should omit weekends, but for some reason the formula i am using does not even with the add-in. Maybe there is an error within the formula that is stopping this? "David Biddulph" wrote: WORKDAY shouldn't need a list of weekends. The second (optional) parameter is a list of holidays. Weekends get omitted anyway. -- David Biddulph "DaveAsh" wrote in message ... Yeah, sorry. I now have the following: =WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2),AA1:AA34)+MO D(E38,1)+VLOOKUP(J38,$D$1:$F$5,3)+(MOD(E38+VLOOKUP (J38,$D$1:$F$5,3),1)TIME(17,0,0))*2/3 (AA1:AA34 are a list of weekends) .... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to skip holidays
So what is in D1:F5?
Based upon an assumption, how about =((WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2))+MOD(E38,1 ))*(J381))+ ((WORKDAY(E38,1)+MOD(E38,1)-TIME(4,0,0))*(MOD(E38,1)TIME(13,0,0))*(J38=1))+ ((E38+TIME(4,0,0))*(MOD(E38,1)<=TIME(13,0,0))*(J38 =1)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... Yeah, sorry. I now have the following: =WORKDAY(E38,VLOOKUP(J38,$D$1:$F$5,2),AA1:AA34)+MO D(E38,1)+VLOOKUP(J38,$D$1:$F$5,3)+(MOD(E38+VLOOKUP (J38,$D$1:$F$5,3),1)TIME(17,0,0))*2/3 (AA1:AA34 are a list of weekends) The only problem that i have with this though is that when the priority is 1 (4 hours projected time) and the start date/time is after 1pm on a Friday, the formula still gives weekends as a projected finish date. How can i adjust the formula to take this into account? "Bob Phillips" wrote: Didn't I give you this before, where I used the WORKDAY function embedded in part of what you have? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... =LOOKUP(J38,{1,2,3,4,5},E38+{"0.166666666666666666 66666666666667","1","2","7","14"}) J38 is the job priority (1-5) 1-Completion should be within 4 working hours 2-" " " " " 1 day 3-" " " " " 2 days 4-" " " " " 7 days 5-" " " " " 14 days E38 is the job start date I know the formula I am using is not the best but can i add to it a workday and IF function? Or is there a better way to do this? Thanks. "Bob Phillips" wrote: Show us your formula, and we might be able to help. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DaveAsh" wrote in message ... Hi, I have a holiday list set up, and a formula that works out the projected completion date of a job in the format dd/mm/yyyy hh:mm. How can i add to the formula so that if a day within the holiday list is predicted it skips to a day after (which is not a holiday), and still gives an accurate date/time e.g Predicted time to complete is say 4 hours start date/time: 28/12/2007 16:40 Finish time should be: 31/12/07 11:40 (The formula takes is to take into account working day (9-5 mon-fri) Please help! Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Workdays-Holidays-adjustments | Excel Worksheet Functions | |||
Skip holidays falling between two dates | Excel Worksheet Functions | |||
Formula - Excluding weekends & holidays | Excel Worksheet Functions | |||
Skip the Holidays 2 | Excel Discussion (Misc queries) | |||
Skip the holidays | Excel Discussion (Misc queries) |