# Date calculation that returns a value from a cell

#### Geoffrey1231

##### New Member
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

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Geoffrey1231 said:
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.

Try these,

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

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

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

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

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

Thank you NBVC!!!

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

Did you mean something like this?

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

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

Replies
3
Views
72
Replies
8
Views
120
Replies
6
Views
243
Replies
5
Views
143
Replies
17
Views
511

1,203,605
Messages
6,056,234
Members
444,852
Latest member
MJaspering

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back