Formula's to count

[Guest]

I need to create a formula in excel to count a resource if their active
period falls in a set of dates. This is the formula that was passed down to
me but I would like to simplfy it.

=IF(YEAR(F7)=2007,(IF(MONTH(F7)=1,(IF(MONTH(F7)=MONTH(H7),0.5,(IF(D7<>"",(IF(("2/1/2007"-E7)>0,(IF(("2/1/2007"-F7)>30,"",(IF(("2/1/2007"-E7)<16,0.5,(IF(("2/1/2007"-F7)>16,0.5,1)))))),"")),"")))),(IF(F7<>"",(IF(("2/1/2007"-E7)>0,(IF(("2/1/2007"-F7)>30,"",(IF(("2/1/2007"-E7)<16,0.5,(IF(("2/1/2007"-F7)>16,0.5,1)))))),"")),"")))),(IF(D7<>"",(IF(("2/1/2007"-E7)>0,(IF(("2/1/2007"-F7)>30,"",(IF(("2/1/2007"-E7)<16,0.5,(IF(("2/1/2007"-F7)>16,0.5,1)))))),"")),"")))

 

[Guest]

Tell us how you want to count it and give some sample data.

Thanks,
Barb Reinhardt

 

[Guest]

to simplify it, I would start by assigning names to
("2/1/2007"-E7) Maybe DTE
("2/1/2007"-F7) Maybe DTF

Then If I was able to follow the logic

If(and(YEAR(F7)=2007,MONTH(F7)=1,MONTH(H7)=1),0.5,if(or(D7<>â€â€,F7<>â€â€),if
(or(DTE<=0,DTF>30),â€â€,if(DTE<16,0.5,if(DTF>16,0.5,1)))

will give the right answer.

 

[Guest]

Additionally I assume that the "2/1/2007" should probably be date(2007,2,1)
unless you are using the d/m/yyyy form and it should be date(2007,1,2)