Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Hello,
I am trying to calulate days to ensure a task was done in 1 business day or 5 business days for particular groups. I know that I can use NETWORKDAYS but here is the issue: If a holiday is returned,within a range of dates, how do I find the next business day and add it to tell me if i day or 5 days? Can anyone assist?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
surely you don't need to find the next business day - if you know when
the task started and when it finished, NetWorkDays will deduct the weekends and holidays to give you the elapsed time - which you can then use to determine if within 1 business day or 5 business days etc I remember a good few years ago now building a very complicated formula to calculate service time which not only had to cater for days, but allow for a task being logged outside working hours and finished outside working hours - it had to bring the relevent date back or forwards as appropriate to ONLY count chargeable elapsed time! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Hmm...Maybe I was not clear...What I need to do is this...lets say I know the
start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this grouo has 5 business days to be completed. I need to then have excel give me the date of the 5th business day excluding the holiday (Juky 4th). Thanx again " wrote: surely you don't need to find the next business day - if you know when the task started and when it finished, NetWorkDays will deduct the weekends and holidays to give you the elapsed time - which you can then use to determine if within 1 business day or 5 business days etc I remember a good few years ago now building a very complicated formula to calculate service time which not only had to cater for days, but allow for a task being logged outside working hours and finished outside working hours - it had to bring the relevent date back or forwards as appropriate to ONLY count chargeable elapsed time! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Don't use NETWORKDAYS(), use WORKDAY(). Look in HELP for details.
-- Kind regards, Niek Otten "Dmorri254" wrote in message ... | Hello, | | I am trying to calulate days to ensure a task was done in 1 business day or | 5 business days for particular groups. I know that I can use NETWORKDAYS but | here is the issue: | | If a holiday is returned,within a range of dates, how do I find the next | business day and add it to tell me if i day or 5 days? | | Can anyone assist?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Try something like this:
With a list of holiday dates in J1:J10 For a start date in A1 B1: =WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10) Note_1: in case of text wrap, there are no spaces in that formula. Note_2: That formula is part of the Analysis ToolPak addin. If you get the #NAME! error, then the addin enabled (and possilby installed). Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Dmorri254" wrote: Hmm...Maybe I was not clear...What I need to do is this...lets say I know the start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this grouo has 5 business days to be completed. I need to then have excel give me the date of the 5th business day excluding the holiday (Juky 4th). Thanx again " wrote: surely you don't need to find the next business day - if you know when the task started and when it finished, NetWorkDays will deduct the weekends and holidays to give you the elapsed time - which you can then use to determine if within 1 business day or 5 business days etc I remember a good few years ago now building a very complicated formula to calculate service time which not only had to cater for days, but allow for a task being logged outside working hours and finished outside working hours - it had to bring the relevent date back or forwards as appropriate to ONLY count chargeable elapsed time! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
the workday function has a 3rd argument which allows you to supply a list of
holidays - an array of holiday dates or a reference to a range that contains a list of holiday dates WORKDAY(start_date,days,holidays) Easiest to Use it. -- Regards, Tom Ogilvy "Ron Coderre" wrote: Try something like this: With a list of holiday dates in J1:J10 For a start date in A1 B1: =WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10) Note_1: in case of text wrap, there are no spaces in that formula. Note_2: That formula is part of the Analysis ToolPak addin. If you get the #NAME! error, then the addin enabled (and possilby installed). Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Dmorri254" wrote: Hmm...Maybe I was not clear...What I need to do is this...lets say I know the start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this grouo has 5 business days to be completed. I need to then have excel give me the date of the 5th business day excluding the holiday (Juky 4th). Thanx again " wrote: surely you don't need to find the next business day - if you know when the task started and when it finished, NetWorkDays will deduct the weekends and holidays to give you the elapsed time - which you can then use to determine if within 1 business day or 5 business days etc I remember a good few years ago now building a very complicated formula to calculate service time which not only had to cater for days, but allow for a task being logged outside working hours and finished outside working hours - it had to bring the relevent date back or forwards as appropriate to ONLY count chargeable elapsed time! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Tom:
I found inconsistencies in trying to apply WORKDAY "as is" to my interpretation of the OP's situation when the entered StartDate is on a weekend or holiday, assuming no actual work would be done on the weekend. If the goal is to complete a task within 5 workdays, here's what I got using just WORKDAY(StartDate,4) and ignoring holidays (for simplicity): StartDate Calcd_EndDate NetWorkDays Sun, January 01, 2006 Thu, January 05, 2006 4 Mon, January 02, 2006 Fri, January 06, 2006 5 Tue, January 03, 2006 Mon, January 09, 2006 5 Wed, January 04, 2006 Tue, January 10, 2006 5 Thur, January 05, 2006 Wed, January 11, 2006 5 Fri, January 06, 2006 Thu, Jan 12, 2006 5 Sat, January 07, 2006 Thu, Jan 12, 2006 4 Consequently, I adjusted my formula to calc the 4th workday if starting on a weekday or the 5th workday if starting on a weekend or holiday. Of course, if I'm wrong and the startdate should always count as the 1st day then only the WORKDAY function would be necessary. *********** Regards, Ron XL2002, WinXP-Pro "Tom Ogilvy" wrote: the workday function has a 3rd argument which allows you to supply a list of holidays - an array of holiday dates or a reference to a range that contains a list of holiday dates WORKDAY(start_date,days,holidays) Easiest to Use it. -- Regards, Tom Ogilvy "Ron Coderre" wrote: Try something like this: With a list of holiday dates in J1:J10 For a start date in A1 B1: =WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10) Note_1: in case of text wrap, there are no spaces in that formula. Note_2: That formula is part of the Analysis ToolPak addin. If you get the #NAME! error, then the addin enabled (and possilby installed). Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Dmorri254" wrote: Hmm...Maybe I was not clear...What I need to do is this...lets say I know the start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this grouo has 5 business days to be completed. I need to then have excel give me the date of the 5th business day excluding the holiday (Juky 4th). Thanx again " wrote: surely you don't need to find the next business day - if you know when the task started and when it finished, NetWorkDays will deduct the weekends and holidays to give you the elapsed time - which you can then use to determine if within 1 business day or 5 business days etc I remember a good few years ago now building a very complicated formula to calculate service time which not only had to cater for days, but allow for a task being logged outside working hours and finished outside working hours - it had to bring the relevent date back or forwards as appropriate to ONLY count chargeable elapsed time! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Agreed. I didn't read your original post closely enough.
-- Regards, Tom Ogilvy "Ron Coderre" wrote: Tom: I found inconsistencies in trying to apply WORKDAY "as is" to my interpretation of the OP's situation when the entered StartDate is on a weekend or holiday, assuming no actual work would be done on the weekend. If the goal is to complete a task within 5 workdays, here's what I got using just WORKDAY(StartDate,4) and ignoring holidays (for simplicity): StartDate Calcd_EndDate NetWorkDays Sun, January 01, 2006 Thu, January 05, 2006 4 Mon, January 02, 2006 Fri, January 06, 2006 5 Tue, January 03, 2006 Mon, January 09, 2006 5 Wed, January 04, 2006 Tue, January 10, 2006 5 Thur, January 05, 2006 Wed, January 11, 2006 5 Fri, January 06, 2006 Thu, Jan 12, 2006 5 Sat, January 07, 2006 Thu, Jan 12, 2006 4 Consequently, I adjusted my formula to calc the 4th workday if starting on a weekday or the 5th workday if starting on a weekend or holiday. Of course, if I'm wrong and the startdate should always count as the 1st day then only the WORKDAY function would be necessary. *********** Regards, Ron XL2002, WinXP-Pro "Tom Ogilvy" wrote: the workday function has a 3rd argument which allows you to supply a list of holidays - an array of holiday dates or a reference to a range that contains a list of holiday dates WORKDAY(start_date,days,holidays) Easiest to Use it. -- Regards, Tom Ogilvy "Ron Coderre" wrote: Try something like this: With a list of holiday dates in J1:J10 For a start date in A1 B1: =WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10) Note_1: in case of text wrap, there are no spaces in that formula. Note_2: That formula is part of the Analysis ToolPak addin. If you get the #NAME! error, then the addin enabled (and possilby installed). Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Dmorri254" wrote: Hmm...Maybe I was not clear...What I need to do is this...lets say I know the start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this grouo has 5 business days to be completed. I need to then have excel give me the date of the 5th business day excluding the holiday (Juky 4th). Thanx again " wrote: surely you don't need to find the next business day - if you know when the task started and when it finished, NetWorkDays will deduct the weekends and holidays to give you the elapsed time - which you can then use to determine if within 1 business day or 5 business days etc I remember a good few years ago now building a very complicated formula to calculate service time which not only had to cater for days, but allow for a task being logged outside working hours and finished outside working hours - it had to bring the relevent date back or forwards as appropriate to ONLY count chargeable elapsed time! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
WOW thanx much you guys...I think we came up with the answer...we created a
lookup for internal holidays..and you are correct we needed to exclude weekends and holidays...so we used =WORKDAY(H2,5,Holidays)..against the start time of the task...this created a target date..then to determine if the work was done ontime, we did the following against the closed time of the task(if is was balnk then we used TODAY).......=IF(I2=IF(D2="",TODAY(),D2),IF(D2="" ,"Time Remaining","Sla Met"),"Sla Not Met") Thanx for the help "Ron Coderre" wrote: Tom: I found inconsistencies in trying to apply WORKDAY "as is" to my interpretation of the OP's situation when the entered StartDate is on a weekend or holiday, assuming no actual work would be done on the weekend. If the goal is to complete a task within 5 workdays, here's what I got using just WORKDAY(StartDate,4) and ignoring holidays (for simplicity): StartDate Calcd_EndDate NetWorkDays Sun, January 01, 2006 Thu, January 05, 2006 4 Mon, January 02, 2006 Fri, January 06, 2006 5 Tue, January 03, 2006 Mon, January 09, 2006 5 Wed, January 04, 2006 Tue, January 10, 2006 5 Thur, January 05, 2006 Wed, January 11, 2006 5 Fri, January 06, 2006 Thu, Jan 12, 2006 5 Sat, January 07, 2006 Thu, Jan 12, 2006 4 Consequently, I adjusted my formula to calc the 4th workday if starting on a weekday or the 5th workday if starting on a weekend or holiday. Of course, if I'm wrong and the startdate should always count as the 1st day then only the WORKDAY function would be necessary. *********** Regards, Ron XL2002, WinXP-Pro "Tom Ogilvy" wrote: the workday function has a 3rd argument which allows you to supply a list of holidays - an array of holiday dates or a reference to a range that contains a list of holiday dates WORKDAY(start_date,days,holidays) Easiest to Use it. -- Regards, Tom Ogilvy "Ron Coderre" wrote: Try something like this: With a list of holiday dates in J1:J10 For a start date in A1 B1: =WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10) Note_1: in case of text wrap, there are no spaces in that formula. Note_2: That formula is part of the Analysis ToolPak addin. If you get the #NAME! error, then the addin enabled (and possilby installed). Is that something you can work with? *********** Regards, Ron XL2002, WinXP-Pro "Dmorri254" wrote: Hmm...Maybe I was not clear...What I need to do is this...lets say I know the start time, (Monday July 3rd 2006 the 4th is a holiday) I know that this grouo has 5 business days to be completed. I need to then have excel give me the date of the 5th business day excluding the holiday (Juky 4th). Thanx again " wrote: surely you don't need to find the next business day - if you know when the task started and when it finished, NetWorkDays will deduct the weekends and holidays to give you the elapsed time - which you can then use to determine if within 1 business day or 5 business days etc I remember a good few years ago now building a very complicated formula to calculate service time which not only had to cater for days, but allow for a task being logged outside working hours and finished outside working hours - it had to bring the relevent date back or forwards as appropriate to ONLY count chargeable elapsed time! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Ron Coderre Wrote: =WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10) Hi Ron, doesn't look like there was a need to consider start date on a holida but if there was you could use =WORKDAY(WORKDAY(A1,-1,J$1:J$10),5,J$1:J$10 -- daddylongleg ----------------------------------------------------------------------- daddylonglegs's Profile: http://www.excelforum.com/member.php...fo&userid=3048 View this thread: http://www.excelforum.com/showthread.php?threadid=53847 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Business Days Including Holidays
Hi, DL
1)On the train home from Boston, I was thinking that my original formula could stand some improvements. You took care of that nicely. 2)Regarding: doesn't look like there was a need to consider start date on a holiday<< I allowed that the start date might be logged by a help desk that's takes calls 24/7 but the SLA only includes standard workdays. Whether that's true or not, the new formula calculates the right target date. *********** Best Regards, Ron XL2002, WinXP-Pro "daddylonglegs" wrote: Ron Coderre Wrote: =WORKDAY(A1,4+OR(WEEKDAY(A1,2)5,ISNUMBER(MATCH(A1 ,$J$1:$J$10,0))),$J$1:$J$10) Hi Ron, doesn't look like there was a need to consider start date on a holiday but if there was you could use =WORKDAY(WORKDAY(A1,-1,J$1:J$10),5,J$1:J$10) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=538475 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Employee days worked (-Holidays, -weekends, Snow Days, etc) | Excel Discussion (Misc queries) | |||
excel total days formula including weekends, excluding holidays | Excel Discussion (Misc queries) | |||
Calculating business days minus holidays | Excel Worksheet Functions | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions | |||
Generating business days in a calendar month, EXCLUDING holidays | Excel Worksheet Functions |