SUMPRODUCT used to calculate Number of weekdays, returns #VALUE in VBA

**suchi0508** · 10-30-2012, 08:05 AM

Hi

i am trying to calculate number of week days between 2 dates.

In my Excel named 'Leave Detail', Cell H8 = Starting Date and Cell I8 = End Date i tried to save the return value in Cell J8 but it is populated with '#VALUE'.

Sheets("Leave Detail").Range("J8").Value = Evaluate("SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT('Leave Detail'!H8:Leave Detail'!I8)),2)<6))")

Please help.

**kbkumar** · 10-30-2012, 08:13 AM

Hi Suchi508,

Welcome to the forum.

Try the below code:

Please Login or Register  to view this content.

Hope this helps.

**kbkumar** · 10-30-2012, 08:14 AM

Just to add, Network days is a function that captures weekdays between any two sets of dates

**suchi0508** · 10-30-2012, 08:21 AM

now J8 is populated with #NAME? .

can you please confirm, for NETWORKDAYS() should i have 'AnalysisToolPak'? that is not installed in my machine not i am able to do so.

**kbkumar** · 10-30-2012, 08:28 AM

Think you need to load the Analysis ToolPak add-in. Follow these steps:

http://office.microsoft.com/en-gb/ex...005209190.aspx

**suchi0508** · 10-30-2012, 08:38 AM

is there any other way to do this? because i tried to install it in the same way but didn't work. in addition to that , this excel may be used by other people as well, so its better if there is any other way to do this.

**kbkumar** · 10-30-2012, 08:45 AM

Try the below modified version:

Please Login or Register  to view this content.

Hope this helps.

**suchi0508** · 10-30-2012, 09:08 AM

Thanks a lot. it works, but one more request, can you please let me know how to calculate the number of working day after deduction of the holiday,

where holiday list is saved in another sheet, maned 'Accrual' and cell = B26 to B28

when we calculate in Excel, then we can use something like, SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(H8&":"&I8)),2)<6),--ISNA(MATCH(ROW(INDIRECT(H8&":"&I8)),Accural!$B$26:Accural!$B$28,0))),"")

but i am not sure how to do it on VBA.

**kbkumar** · 10-30-2012, 09:20 AM

Are you able to upload a sample workbook?

**kbkumar** · 10-30-2012, 09:31 AM

In case you want to exclude any holidays this could be easily accomplished by using the =networkdays() function.

Please Login or Register  to view this content.

If you can play around with your excel setting to enable Analysis Tool Pack, you may get to have this work for you in a very simple manner.

**daddylonglegs** · 10-30-2012, 09:40 AM

Originally Posted by kbkumar

Please Login or Register  to view this content.

This doesn't work, does it? if H8 is today (30th October) and I8 is 6 days later (5 November) I get a result of 1

This formula will give you the same result as NETWORKDAYS

=SUM(INT((WEEKDAY(H8-{2,3,4,5,6})+I8-H8)/7))

and to take out holidays try

=SUM(INT((WEEKDAY(H8-{2,3,4,5,6})+I8-H8)/7))-SUMPRODUCT((Accural!$B$26:$B$28>=H8)*(Accural!$B$26:$B$28<=I8)*(WEEKDAY(Accural!$B$26:$B$28,2)>6))

**kbkumar** · 10-30-2012, 09:49 AM

Hi Daddylonglegs: You are right. I tried some random historic dates and they seemed to be fine (wierd that it fails with your sample dates!). Could you please point why it fails as the logic seems to be ok?

**suchi0508** · 10-30-2012, 10:02 AM

i tried the same as Sheets("Leave Detail").Range("J8").Value = Evaluate("SUM(INT((WEEKDAY(H8-{2,3,4,5,6})+I8-H8)/7))-SUMPRODUCT((Accural!$B$26:$B$28>=H8)*(Accural!$B$26:$B$28<=I8)*(WEEKDAY(Accural!$B$26:$B$28,2)>6))")

but J8 popupled with #VALUE!

i am attaching Sample Excel for your reference. But please be aware from running the Macro. it's going to Infinite Loop.

My Objective is to calculate Number of working Day per week after deducting the Leave and Holiday.