Looking for alternative to NETWORKDAYS.INTL

**Jaron_t** · 05-16-2012, 01:06 PM

Hello. I am looking for a way to count the number of work days between two dates. However rather than being a constant 2 day weekend, I need to be able to choose how long a work week is. Also, rather than having to adjust the formula for every scenario, I would like it to reference cells that contain the data for the formula.

In the attached example, I have a formula in D2 that does a similar function. It takes the following variable data:
1. A1: Job duration in work days
2. B2: Number of work days in a week
3. C2: Job start date

....and returns the end date of the job in cell D2.

What I need is the opposite. I need to be able to enter:
1. Number of work days in a week
2. Job start date
3. Job end date

.... and return the Job duration in work days

I am not a proficient excel user. It is probably simple to tweak the formula I already have but I don't understand how it works; I just found it on the internet. From what I've read, Networkdays.Intl seems like a solution, but I don't have that option on my version of excel.

Thank you in advance for any suggestions you may have.

Jaron

PS: for any visitors who are unable to download the example, the formula in D2 is as follows: =C2-WEEKDAY(C2,3)+INT(7/B2*(A2+MIN(5,WEEKDAY(C2,3))))

**daddylonglegs** · 05-16-2012, 01:51 PM

Hello Jaron,

Wouldn't you need to define which days are the workdays in a week rather than just the number?

.......or are you making some assumptions about that - if there are 4 workdays in a week then will that be Monday to Thursday, Tuesday to Friday or something else?

**Jaron_t** · 05-16-2012, 02:19 PM

You are right...which tells me that the formula I am already using is wrong. If I enter 6/4/12 ( a Monday) as a start date, select 4 days in a work week, and define Job duration as 14 days, and assuming the work days are Mon-Thurs, the current formula needs to return 6/27/12 as the "finished by" date. Instead it returns 6/28/12.

I guess this means I need help with two formulas: one to return value D if A, B, and C are known. The other to return value A if B, C, and D are known.

**Jaron_t** · 05-17-2012, 02:47 PM

bump no response

**Jaron_t** · 05-21-2012, 04:54 PM

Ok, first of all, I apologize for last post (actually 2nd to last). If I confused everyone, its because my original formula was in fact working correctly; its just my math skills that need tuning up.

I did make one adjustment, though. The formula to find the end date (D) divides the days in a week (7, a constant) by the selected number of workdays (in the example, 5 in column B) to provide a multiplier which it then reduces to an integer and applies to the job duration (A). Next it takes this figure and adds it to my start date (C). This process treats the start date as a whole day in addition to the modified job duration, so that I end up with one extra day. I modified it to remove the start date as follows (in example #2, cell D2):

=C2-WEEKDAY(C2,3)+INT(7/B2*((A2-1)+MIN(5,WEEKDAY(C2,3))))

where:
A2=Job duration in work days
B2=work days in a week
C2=Start date
D2=contains the formula and returns the end date given the known criteria

Now, back to my original dilemma:
I believe I have figured out a formula to tell me job duration in work days if the other three criteria are known. In example #2, cell A3, the formula is:

=ROUNDUP(((D3+1)-C3)/(7/B3),0)

where:
A2=contains the formula and returns the Job duration in work days given the known criteria.
B2=Work days in a week
C2=Start date
D2=End date

I believe I can mark this as solved, but I'll wait a couple of days to see if there is any feedback. As I said, I'm an excel beginner and there could be something wrong with these formulas and I don't know it.

Thanks,
Jaron