Date calculation that returns a value from a cell

[Geoffrey1231]

I'm a novice with excel and am trying to do something that does not seem to be straight forward. (B2) indicates an employee is Full time or part time by showing either 1 or .5 (This is based on an if, then statement in cell (B2). I would like to compare cell (B3) which is the hire date to today's date as well as compare cell (B4), which is the termination date to today's date. If the hire date is less than or = today's date and the termination date is greater than or = today's date, I would like the result to be what's in cell (B2). Otherwise, result should be 0. I have a need for another forumula in another column which would be the same as above, but would also look at an additional date (B5) which I would like to compare to today's date and if it is less than or equal to today's date it would yield the result of (B2)
Any help would be appreciated,
Geoff

 

[Brian from Maui]

I'm a novice with excel and am trying to do something that does not seem to be straight forward. (B2) indicates an employee is Full time or part time by showing either 1 or .5 (This is based on an if, then statement in cell (B2). I would like to compare cell (B3) which is the hire date to today's date as well as compare cell (B4), which is the termination date to today's date. If the hire date is less than or = today's date and the termination date is greater than or = today's date, I would like the result to be what's in cell (B2). Otherwise, result should be 0. I have a need for another forumula in another column which would be the same as above, but would also look at an additional date (B5) which I would like to compare to today's date and if it is less than or equal to today's date it would yield the result of (B2)
Any help would be appreciated,
Geoff

It seems you want a nested IF/AND function,

=IF(AND(HireDate<=Today,TerminationDate>=Today),0,B2)

I'm not sure if the logic is valid, but hopefully it'll get you started.

 

[NBVC]

Try these,


First Formula,
=IF(AND(B3<=NOW(),B4>=NOW()),B2,0)

Second Formula.
=IF(AND(B3<=NOW(),B4>=NOW(),B5<=NOW()),B2,0)

 

[Geoffrey1231]

Thank you NBVC and Brian from Maui! It worked!!

 

[Geoffrey1231]

I have a follow up to my original question; I also have a field (a2) that has a drop down list. If the option chosen in the drop down list is "supervisor", the result should be "0" in the second formula.

You guys have been very helpful,

Geoff

 

[NBVC]

Is it a drop-down as in a Data Validation List?

If so,
=IF(A2="supervisor",0,IF(AND(B3<=NOW(),B4>=NOW(),B5<=NOW()),B2,0))

If it is a true drop-down (i.e. a combo box), then what is the cell link, and were is "supervisor" in the list. i.e. if choosing Supervisor from the list returns a number, say 2, in the linked cell, then the formula might be something like,

=IF(A2=2,0,IF(AND(B3<=NOW(),B4>=NOW(),B5<=NOW()),B2,0)), where A2 is the linked cell and 2 represents the position in the list of the work supervisor.

Good Luck

 

[Geoffrey1231]

Thank you NBVC!!!

 

[Geoffrey1231]

Ok...I think this is my last follow up to this topic...Can I add an additional possibility to B4 and B5. Meaning, if B4 is blank then it should return the value in B2 and if B5 is blank it should return 0. I was thinking that incoporating the word "or" in the if statement for these two cells somehow would work.....at least that's the idea. And yes, it is a data validation list.

Once again,
You all have been very helpful,

Geoff

 

[NBVC]

Did you mean something like this?

=IF(OR(A2="supervisor",B5=""),0,IF(AND(B3<=NOW(),OR(B4>=NOW(),B4=""),B5<=NOW()),B2,0))

 

[Geoffrey1231]

NBVC...Your answers have been perfect. Unfortunately, my inexperience has led me to more follow-up questions;
I have a data validation list for cell B2 which I have changed a bit. There will be 8 choices (FT phone, P.T. Phone 20 HRS, P.T. phone 25 hours, P.T. phone 30 hours, FT non-phone, P.T. non-phone 20 hrs, P.T. non-phone 25 hours and P.T. non-phone 30 hours.) I want B3 to return a value of 0 if B2 is empty, a value of 1 for FT phone, a value of .5 for P.T. phone 20 hrs, a value of .625 for P.T. phone 25 hours, A value of .75 for P.T. phone 30 hours, a value of 1 for FT non-phone, a value of .5 for P.T. non-phone 20 hrs, a value of .625 for P.T. phone 25 hours and a value of .75 for P.T. non-phone 30 hours. What would the if statement be for this?
Next, if you look at the first formula you gave me back in my original question, I would also like to return a value of 0 if B3 is blank. But also return a value of cell B2 if B3 is <= NOW and B4 is blank.
And finally, I'm now trying to add in the additional non-phone options to your last "if" statement. Anotherwords, where you said (A2="supervisor", B5="") I would like to have this same logic applied to any of the 4 non-phone choices. So if the option is P.T. non-phone staff (20 hours), then the value returned should be 0 as well as for the other 3 non-phone options in the data validation list.

Thank you for your patience,

Geoff