Nested If? Or? And?

[LValDean]

I have given up the order and ghost of what this should look like. This is a view of the worksheet:

F G H I J K
Start Date 3/4/2012 4/30/2012 8/20/2012
Finish Date 4/28/2012 8/18/2012 10/13/2012
working days 40 78 38
Start Date Finish Date FTE Prep Blueprint Global
3/4/2012 8/30/2013 1.00 40 78 38
3/4/2012 4/28/2012 1.00 40 0 0
3/4/2012 2/28/2014 1.00 40 78 38
4/18/2012 1/31/2013 1.00 12 78 38
4/18/2012 9/30/2013 1.00 12 78 38
4/18/2012 10/10/2014 1.00 12 78 38
3/4/2012 2/8/2014 1.00 40 78 38

What the calculation needs to look for is the start and finish dates of any single resource (this is just a slice there are over 50 resources and an additional 6 phases extending out to 2014).

* Not all resources are FTE 1
* Not all resources start or stop at the Beginning or end of a Phase, in fact most come in or leave during the Phase
* Holidays have to be calculated (they are within the working days)

I am manually 'fixing' the days because I can't seem to fix the calculation. This is the calculation right now.

=IF(AND($F6<=I$2,$G6>=I$3),$H6*I$4,0)

It only works if a resource is full time (1 FTE) and begins at the exact start of a phase and ends at the exact end of a phase.

I have tried "Or" statements without success. I need to extend the calculation to consider a date in column 'f' that is > I2 but < I3 and calculate the working days of that phase only + this to all other phases. I think I am just an idiot and the answer is in front of me.

Any assistance would be greatly appreciated.

 

[BlueHornet]

Welcome to the board, LValDean.

May I suggest that you visit the page of useful links in the Forum at http://www.mrexcel.com/forum/showthread.php?t=628649 and take a look at the Add-Ins page for HTML Maker.

If you would install that add-in and use it (it's pretty simple; if I can do it, you can too) then you'll be able to re-post with a much better display of your spreadsheet, including current formulas (which you won't have to cut and paste individually).

I'll be watching for your re-post and see what I can do about helping you out then.

 

[gnrboyd]

I was a little unclear as to what your exact goal was, but I took a shot based upon what I saw.

I assumed you didn't want to count weekend days in your count which is what the NETWORKDAYS formula is used for. I assumed that "Prep" is the same as "Phase 1", "Blueprint" is "Phase 2" and "Global" is "Phase 3". I noticed that your totals in the prep column for 3 rows equals 12 but my figures are 8. The other totals are the same. Is the 12 figure by chance incorrect in your data? If not, please further explain.... or as BlueHornet recommended, post an example of your spreadsheet for better visual clarity using an HTML converter ad-in.

The table to the right in my example is used in the NETWORKINGDAYS formula to extract out holidays in the count. I used a federal holiday schedule found in the web but your dates may vary. You will just need to include in this list each date that your company considers a holiday. You can modify the table size in the formula for your needs depending on if this spreadsheet is an on-going thing, if you start a new one each year, etc. (My table is M2:M49 in the formula) The holidays will just need to cover the period of the earliest start date to the last finish date in your data.

I assumed FTE was Full Time Employee or Full Time Equivalent. Your data contained only FTE = 1. When you change this figure, it simply just multiplies that figure times the number of working days. Again, I assumed that is what your intent was.

Hope this helps and is at least fairly close to what you were looking for.

Mr. Excel 05-05-12.xls
	F	G	H	I	J	K	L	M	N
1				Phase1	Phase2	Phase3		Holidays	Description
2	StartDate			3/4/2012	4/30/2012	8/20/2012		5/28/2012	MemorialDay
3	FinishDate			4/28/2012	8/18/2012	10/13/2012		7/4/2012	IndependenceDay
4	WorkingDays			40	78	38		9/3/2012	LaborDay
5	StartDate	FinishDate	FTE	Prep	Blueprint	Global		10/8/2012	ColumbusDay
6	3/4/2012	8/30/2013	1	40	78	38		11/12/2012	VeteransDay
7	3/4/2012	4/28/2012	1	40	0	0		11/22/2012	ThanksgivingDay
8	3/4/2012	2/28/2014	1	40	78	38		12/25/2012	ChristmasDay
9	4/18/2012	1/31/2013	1	8	78	38		1/1/2013	NewYearsDay
10	4/18/2012	9/30/2013	1	8	78	38		1/21/2013	BirthdayofMartinLutherKing,Jr.
11	4/18/2012	10/10/2014	1	8	78	38		2/18/2013	WashingtonsBirthday
12	3/4/2012	2/8/2014	1	40	78	38		5/27/2013	MemorialDay
13								7/4/2013	IndependenceDay
14								9/2/2013	LaborDay
15								10/14/2013	ColumbusDay
16								11/11/2013	VeteransDay
17								11/28/2013	ThanksgivingDay
18								12/25/2013	ChristmasDay
19								1/1/2014	NewYearsDay
20								1/20/2014	BirthdayofMartinLutherKing,Jr.
21								2/17/2014	WashingtonsBirthday
22								5/26/2014	MemorialDay
23								7/4/2014	IndependenceDay
24								9/1/2014	LaborDay
25								10/13/2014	ColumbusDay
26								11/11/2014	VeteransDay
27								11/27/2014	ThanksgivingDay
28								12/25/2014	ChristmasDay
Sheet1

 

[LValDean]

Hi, thanks for this. I can tell you the difference is only in the Holiday count. Several are not considered in our calculation.

I will test this, used in the past and I think we ran into the problem where we had resources not being counted as they rolled into phases, that was why I shifted to the "if" statement.

 

[LValDean]

Welcome to the board, LValDean.

May I suggest that you visit the page of useful links in the Forum at http://www.mrexcel.com/forum/showthread.php?t=628649 and take a look at the Add-Ins page for HTML Maker.

If you would install that add-in and use it (it's pretty simple; if I can do it, you can too) then you'll be able to re-post with a much better display of your spreadsheet, including current formulas (which you won't have to cut and paste individually).

I'll be watching for your re-post and see what I can do about helping you out then.

I am trying to figure this tool out so I can show both the table and formula, following the instructions and have it installed, thus far I am only posting the actual HTML. So I am doing something wrong. I will get it sorted and posted. In the mean time, the project budget must be completed!

 

[BlueHornet]

You're probably not doing anything wrong. The HTML is what shows up in your dialog box when you post it - ugly as sin! - but it should resolve when you do a "preview post". Have you tried the preview?

 

[gnrboyd]

Hi, thanks for this. I can tell you the difference is only in the Holiday count. Several are not considered in our calculation.

With this method, you are in full control of the holiday count. You simply enter the dates your company considers holidays into the table. If it is a Monday-Friday and the date is not in the table, it is counted.

I will test this, used in the past and I think we ran into the problem where we had resources not being counted as they rolled into phases, that was why I shifted to the "if" statement.

The formula in my example counts working days (M-F, less holidays) that overlap between the phase and resource date ranges.