Calculate Workdays

Hi,

I have the following function:

=IF(G13="",IF(H13>1,1,IF(G13>1,workdays(H13,G13,Holidays))))


The intent is to calculdate the completion date against the assigned date as follows:

G13 - Typically the assigne date but sometimes can be blank because someone forgets or doesn't bother to insert

For this example, let's say: 12/17/12

H13 - Completion date

For this example, let's say: 12/20/12


I'm expecting a result of '3' for 3 days

The first half of the function is intended to calculate 1 day if G13 is blank or not defined. However, when I have dates in both cells, I'm getting a "false" answer instead of a numerical value that I'm expecting.

Please advise...


In our workplace, we do work weekends & sometimes 7 days a week.

Thanks

hi MyCon. not sure how you plan to handle working on weekends sometimes. but here is for a 5 day week:
=IF(AND(G13="",H13<>""),1,IF(G13<>"",NETWORKDAYS(G13,H13,Holidays)-1))

Hi Group,

I almost managed to resolve my own question by using:

=IF(G13>0,H13-G13,IF(G13="",IF(H13="","",IF(G13="",1))))


Now, I'm sure it's something simple, how do I factor in the holiday porition to this fuction?

Thanks

maybe an array like:
=IF(AND(G13="",H13<>""),1,IF(G13>1,H13-G13-SUM(--(ROW(INDIRECT(G13&":"&H13))=TRANSPOSE(Holidays)))))

press CTRL + SHIFT + ENTER to confirm

Going back to the original post itself..wouldn't the below work?

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

for no weekends

Hi Everyone,

With the use of Networkdays, I thought that excludes weekends. This would be acceptable but we often work weekends.

Perhaps, I have the wrong intent, but wouldn't Wordays include weekends?

The only thing I want to exclude is the holidays, which I already have a named range for.

Thanks

WORKDAY excludes weekends - which version of Excel are you using? If you have Excel 2010 then you can use WORKDAY.INTL function which can be customised to count all weekdays but exclude holidays

Hi Daddylonglegs,

I'm using Excel 2007.

Hi,

Does anyone have a solution that give me the difference between two days,regardless if weekend was worked but not include holidays, using Excel 2007?

Thanks

Bit of a workaround using the NETWORKDAYS.INTL function

Please Login or Register  to view this content.

Actually with NETWORKDAYS.INTL you can specify a string of 0/1s to indicate which days are working days, so for all 7 days this would work

=IF(G13="",1,NETWORKDAYS.INTL(G13,H13,"0000000",Holidays))

....but NETWORKDAYS.INTL isn't available in Excel 2007 so for that version try this formula to exclude holidays only

=IF(G13="",1,H13-G13+1-SUMPRODUCT((Holidays>=G13)*(Holidays<=H13)))

Hi Daddylonglegs,

This function works quite well - Thanks again....
=IF(G13="",1,H13-G13+1-SUMPRODUCT((Holidays>=G13)*(Holidays<=H13)))

Hi Everyone Else,

Thank you very much for your suggestions too...