| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
NETWORKDAYS alternative, for use without Analysis ToolPak
I'm trying to build a formula to calculate the number of working days /
hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consulted http://www.cpearson.com/excel/DateTimeWS.htm and modified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
How about...
=A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmand modified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
I'm confused now. That formula gives me a number of days between 2 dates, as
long as there is no time entered. So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what I want, overall. But it doesn't take the available hours of a working day into account. Anyway, moving on... The formula I have so far is: =IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart))) StartDateTime is a cell containing the start date/time value. StartDateTime is a cell containing the end date/time value. WDStart is a cell containing the start time of a working day WDEnd is a cell containing the end time of a working day PublicHolidays is a named range containing the dates of holidays (to be excluded from count) This works, perfectly - gives me exactly what I want as an answer. What I am trying to do is replace the NETWORKDAYS function in the above formula, so I can distribute the formula to users who cannot use the Analysis ToolPak. -- Olly "vezerid" wrote in message ps.com... How about... =A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmand modified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
Olly,
NETWORKDAYS, as far as I know, does not calculate time, only days. The formula I gave you could replace the call to networkdays in your formula. Does this help? Kostis On Feb 15, 7:50 pm, "Olly" wrote: I'm confused now. That formula gives me a number of days between 2 dates, as long as there is no time entered. So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what I want, overall. But it doesn't take the available hours of a working day into account. Anyway, moving on... The formula I have so far is: =IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart))) StartDateTime is a cell containing the start date/time value. StartDateTime is a cell containing the end date/time value. WDStart is a cell containing the start time of a working day WDEnd is a cell containing the end time of a working day PublicHolidays is a named range containing the dates of holidays (to be excluded from count) This works, perfectly - gives me exactly what I want as an answer. What I am trying to do is replace the NETWORKDAYS function in the above formula, so I can distribute the formula to users who cannot use the Analysis ToolPak. -- Olly "vezerid" wrote in message ps.com... How about... =A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
No. Your formula does not give the exact same results as the networkdays
function... "vezerid" wrote in message oups.com... Olly, NETWORKDAYS, as far as I know, does not calculate time, only days. The formula I gave you could replace the call to networkdays in your formula. Does this help? Kostis On Feb 15, 7:50 pm, "Olly" wrote: I'm confused now. That formula gives me a number of days between 2 dates, as long as there is no time entered. So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what I want, overall. But it doesn't take the available hours of a working day into account. Anyway, moving on... The formula I have so far is: =IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart))) StartDateTime is a cell containing the start date/time value. StartDateTime is a cell containing the end date/time value. WDStart is a cell containing the start time of a working day WDEnd is a cell containing the end time of a working day PublicHolidays is a named range containing the dates of holidays (to be excluded from count) This works, perfectly - gives me exactly what I want as an answer. What I am trying to do is replace the NETWORKDAYS function in the above formula, so I can distribute the formula to users who cannot use the Analysis ToolPak. -- Olly "vezerid" wrote in message ps.com... How about... =A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
Hello Olly,
Can you confirm exactly what your requirements a Do you need to exclude holidays? Are your start and end times/dates always within business hours or might they be outside these (i.e. at evenings, weekends, holidays)? This formula will calculate business hours between your start and end times/dates =(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1) notes: Result cell should be formatted as [h]:mm. If you want the result in decimal number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula by 24 and format result cell as number StartDateTime and EndDateTime should be within business hours [if you can't guarantee that then this can still be done but you need a more complex formula] PublicHolidays should be a single column or single row range "Olly" wrote: No. Your formula does not give the exact same results as the networkdays function... "vezerid" wrote in message oups.com... Olly, NETWORKDAYS, as far as I know, does not calculate time, only days. The formula I gave you could replace the call to networkdays in your formula. Does this help? Kostis On Feb 15, 7:50 pm, "Olly" wrote: I'm confused now. That formula gives me a number of days between 2 dates, as long as there is no time entered. So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what I want, overall. But it doesn't take the available hours of a working day into account. Anyway, moving on... The formula I have so far is: =IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart))) StartDateTime is a cell containing the start date/time value. StartDateTime is a cell containing the end date/time value. WDStart is a cell containing the start time of a working day WDEnd is a cell containing the end time of a working day PublicHolidays is a named range containing the dates of holidays (to be excluded from count) This works, perfectly - gives me exactly what I want as an answer. What I am trying to do is replace the NETWORKDAYS function in the above formula, so I can distribute the formula to users who cannot use the Analysis ToolPak. -- Olly "vezerid" wrote in message ps.com... How about... =A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
This formula will work when StartDateTime and EndDateTime are any time, even
at weekend, evenings etc. =(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATC H(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD( EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(I NT(StartDateTime),PublicHolidays,0)))*MOD(StartDat eTime,1),WDEnd,WDStart) "daddylonglegs" wrote: Hello Olly, Can you confirm exactly what your requirements a Do you need to exclude holidays? Are your start and end times/dates always within business hours or might they be outside these (i.e. at evenings, weekends, holidays)? This formula will calculate business hours between your start and end times/dates =(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1) notes: Result cell should be formatted as [h]:mm. If you want the result in decimal number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula by 24 and format result cell as number StartDateTime and EndDateTime should be within business hours [if you can't guarantee that then this can still be done but you need a more complex formula] PublicHolidays should be a single column or single row range "Olly" wrote: No. Your formula does not give the exact same results as the networkdays function... "vezerid" wrote in message oups.com... Olly, NETWORKDAYS, as far as I know, does not calculate time, only days. The formula I gave you could replace the call to networkdays in your formula. Does this help? Kostis On Feb 15, 7:50 pm, "Olly" wrote: I'm confused now. That formula gives me a number of days between 2 dates, as long as there is no time entered. So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what I want, overall. But it doesn't take the available hours of a working day into account. Anyway, moving on... The formula I have so far is: =IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart))) StartDateTime is a cell containing the start date/time value. StartDateTime is a cell containing the end date/time value. WDStart is a cell containing the start time of a working day WDEnd is a cell containing the end time of a working day PublicHolidays is a named range containing the dates of holidays (to be excluded from count) This works, perfectly - gives me exactly what I want as an answer. What I am trying to do is replace the NETWORKDAYS function in the above formula, so I can distribute the formula to users who cannot use the Analysis ToolPak. -- Olly "vezerid" wrote in message ps.com... How about... =A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
Brilliant! Many, many thanks.
-- Olly "daddylonglegs" wrote in message ... This formula will work when StartDateTime and EndDateTime are any time, even at weekend, evenings etc. =(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATC H(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD( EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(I NT(StartDateTime),PublicHolidays,0)))*MOD(StartDat eTime,1),WDEnd,WDStart) "daddylonglegs" wrote: Hello Olly, Can you confirm exactly what your requirements a Do you need to exclude holidays? Are your start and end times/dates always within business hours or might they be outside these (i.e. at evenings, weekends, holidays)? This formula will calculate business hours between your start and end times/dates =(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1) notes: Result cell should be formatted as [h]:mm. If you want the result in decimal number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula by 24 and format result cell as number StartDateTime and EndDateTime should be within business hours [if you can't guarantee that then this can still be done but you need a more complex formula] PublicHolidays should be a single column or single row range "Olly" wrote: No. Your formula does not give the exact same results as the networkdays function... "vezerid" wrote in message oups.com... Olly, NETWORKDAYS, as far as I know, does not calculate time, only days. The formula I gave you could replace the call to networkdays in your formula. Does this help? Kostis On Feb 15, 7:50 pm, "Olly" wrote: I'm confused now. That formula gives me a number of days between 2 dates, as long as there is no time entered. So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what I want, overall. But it doesn't take the available hours of a working day into account. Anyway, moving on... The formula I have so far is: =IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart))) StartDateTime is a cell containing the start date/time value. StartDateTime is a cell containing the end date/time value. WDStart is a cell containing the start time of a working day WDEnd is a cell containing the end time of a working day PublicHolidays is a named range containing the dates of holidays (to be excluded from count) This works, perfectly - gives me exactly what I want as an answer. What I am trying to do is replace the NETWORKDAYS function in the above formula, so I can distribute the formula to users who cannot use the Analysis ToolPak. -- Olly "vezerid" wrote in message ps.com... How about... =A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
NETWORKDAYS alternative, for use without Analysis ToolPak
I'm getting a #NAME? error when I use this formula. I'm entering"
=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(O222)&":"&INT(AE222))),2 )<6),--ISNA(MATCH(ROW(INDIRECT(INT(O222)&":"&INT(AE222))) ,PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(AE222,2)<6,ISNA(MATCH(INT( AE222),PublicHolidays,0))),MEDIAN(MOD(AE222,1),WDE nd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(O222,2)<6,ISNA(MATCH(INT(O222), PublicHolidays,0)))*MOD(O222,1),WDEnd,WDStart) Should I be inserting the start or end date where you show WDEnd and WDStart as well? I did try that and it didn't seem to help. Thanks in advance for your assistance. "daddylonglegs" wrote: This formula will work when StartDateTime and EndDateTime are any time, even at weekend, evenings etc. =(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATC H(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD( EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(I NT(StartDateTime),PublicHolidays,0)))*MOD(StartDat eTime,1),WDEnd,WDStart) "daddylonglegs" wrote: Hello Olly, Can you confirm exactly what your requirements a Do you need to exclude holidays? Are your start and end times/dates always within business hours or might they be outside these (i.e. at evenings, weekends, holidays)? This formula will calculate business hours between your start and end times/dates =(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1) notes: Result cell should be formatted as [h]:mm. If you want the result in decimal number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula by 24 and format result cell as number StartDateTime and EndDateTime should be within business hours [if you can't guarantee that then this can still be done but you need a more complex formula] PublicHolidays should be a single column or single row range "Olly" wrote: No. Your formula does not give the exact same results as the networkdays function... "vezerid" wrote in message oups.com... Olly, NETWORKDAYS, as far as I know, does not calculate time, only days. The formula I gave you could replace the call to networkdays in your formula. Does this help? Kostis On Feb 15, 7:50 pm, "Olly" wrote: I'm confused now. That formula gives me a number of days between 2 dates, as long as there is no time entered. So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what I want, overall. But it doesn't take the available hours of a working day into account. Anyway, moving on... The formula I have so far is: =IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart))) StartDateTime is a cell containing the start date/time value. StartDateTime is a cell containing the end date/time value. WDStart is a cell containing the start time of a working day WDEnd is a cell containing the end time of a working day PublicHolidays is a named range containing the dates of holidays (to be excluded from count) This works, perfectly - gives me exactly what I want as an answer. What I am trying to do is replace the NETWORKDAYS function in the above formula, so I can distribute the formula to users who cannot use the Analysis ToolPak. -- Olly "vezerid" wrote in message ps.com... How about... =A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)), 2)5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holi days,0)))0)) HTH Kostis Vezerides On Feb 15, 5:54 pm, "Olly" wrote: I'm trying to build a formula to calculate the number of working days / hours between 2 specified dates. This will be used in multiple workbooks, by various users, who may not have the ability to run code or install the Analysis toolpak. I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified the formula accordingly - so far so good! But this still relies on the NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this formula to avoid the networkdays function? -- Olly |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| NETWORKDAYS gives #NAME even though Analysis Toolpak is installed | Excel Worksheet Functions | |||
| How can I get the Analysis Toolpak | Excel Worksheet Functions | |||
| Analysis ToolPak installed but no Data Analysis option | Excel Discussion (Misc queries) | |||
| Analysis Toolpak add in | Excel Worksheet Functions | |||
Linear Mode
