date offset

[bluepenink]

Hello

how can i use offset in the following instance

=IF(AND($J$5>=D$10,$J$5<=D$11),"125",IF(MONTH($J$5)-E$11<=20,OFFSET($R$9,1,),IF(MONTH($J$5)-E$11<=30,OFFSET($R$9,1,),"200")))

basically, J5 = hire date
I10 beginning of the month
I11 = end of month
R9 = 125
R10 = 150
R11 = 175

if person hire date falls between beginning or end of month, then his quota is $125

if first month is 125, then second month is $150, third month is $175 (i tried to use offset) all other months i want to be 200

currently with my formula, everything is coming out to $150 after the first month :S

pls help!!!! thx u

 

[Andrew Poulsom]

The OFFSET function offsets a range, not a date. Can you explain in words what you want your formula to do please?

 

[bluepenink]

hello

thx you for your feedback.

basically,
row 10 contains beginning on the month
row 11 contains end of the month

i have employee hire date in J5

if John Doe is hired in the month of June
i want june = 125 (quota)
July = 150
August = 175

all other months to be 200

that is what i tried to achieve by my offset, but everything after the first month is showing 150.

can u pls help, thx u sir!

 

[Andrew Poulsom]

Can you post a sample of your data please?

 

[bluepenink]

hello

i have attached a sample on sky drive, as everything else appears to be blocked from my work. pls let me know if this works.

https://skydrive.live.com/redir.aspx?cid=592f6aee22d904f4&resid=592F6AEE22D904F4!102&authkey=J1IcVGe5!38%24

much appreciated.

the file name is : sameple_date2

 

[jasonb75]

Based on the information you have given us, try

=IF(I11>$J$5,100+(LOOKUP(DATEDIF($J$5,I11,"M")+1,{1,2,3,4})*25),"")

 

[bluepenink]

Based on the information you have given us, try

=IF(I11>$J$5,100+(LOOKUP(DATEDIF($J$5,I11,"M")+1,{1,2,3,4})*25),"")

hey bud

thxs so much...wow, i was stuck on this for the past few hours....i truely appreciate the help.

i changed the formula (per requirement to)

=IF(I11>$J$5,100+(LOOKUP(DATEDIF($J$5,I11,"M")+1,{1,2,3,4})*25),"200")

also, real quick, is it possible to condition format row 12 (where it says P1, P2...P12) to highlight in yellow) for the first three months only?

thxs so much

i tried placing this in the conditional format....but getting an error...can u help out

=datedif($J$5,I12,"M")="1","="2","="3"

 

[jasonb75]

Try

=IF(I11>$J$5,100+(LOOKUP(DATEDIF($J$5,I11,"M")+1,{1,2,3,4})*25),200)

 

[bluepenink]

thxs alot bud!

also, real quick, is it possible to condition format row 12 (where it says P1, P2...P12) to highlight in yellow) for the first three months only?

thxs so much

i tried placing this in the conditional format....but getting an error...can u help out

=datedif($J$5,I12,"M")="1","="2","="3"

 

[jasonb75]

What's in I12? Assuming the formula I gave you then use

=I12<200 for the conditional format.

Also, change the formula I gave you to

=IF(I11<$J$5,200,100+(LOOKUP(DATEDIF($J$5,I11,"M")+1,{1,2,3,4})*25))

The original one gave the wrong results if the hire date was the last day of the month.