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]

See the help files for VLOOKUP...

 

[NBVC]

Hi Geoff,

It is starting to get a little confusing as to where you want to insert these additional requirements,

At any rate: It looks like it is a totally new formula requirement and based on this we will create a new formula. You would need a vlookup as Brian from Maui suggested, but you would also need to create a small table somewhere off the print area of the sheet. Where did you put your source list for the data validation to work? In the cells of the column to the right of that list enter the corresponding strings, e.g FT phone, etc...

I will assume that list is contained in the table, Y1:Z8, adjust as necessary.

Then in the cell you want the formula in, enter this formula:
=IF(B2="",0,VLOOKUP(B2,Y1:Z8,2,FALSE))

For your second request:

Revise your formula as follows (I assumed you still want to maintain the previous conditions too):
=IF(B3="",0,IF(AND(B3<=NOW(),OR(B4="",B4>=NOW())),B2,0))

I hope this is what you were looking for.

Good Luck.

 

[Geoffrey1231]

Thank you very much Brian from Maui and NBVC. Eventually, I'll get this thing perfect. I'll let you know how this works out.

Geoff

 

[Geoffrey1231]

You had to know this was coming....another follow up question. The good news is that I've gotten the vlookup to work and I only have one formula that isn't quite there.
This is how I'm thinking it...If c5=p.t. non-phone (20hrs), p.t. non-phone 25 hours, p.t. non-phone 30 hours, or full time non-phone the value in cell I5 should=0. If the value in C5 is anything else except "", then I5 should =the value in cell D5, as long as E5<=today and F5 is >=today or "", and G5 is <=today. If G5 is "", then I5 should =0.

I've fooled around with various things to get this to work, to no avail.

On a positive note, this has been quite a learning experience. Oh, and if anyone has a recommendation for a good resource to learn how to write these functions properly, please let me know.

Thanks again,
Geoff

 

[NBVC]

Hi Geoff,

Something like this maybe?

Entered in I5,

=IF(OR(C5="p.t. non-phone (20hrs)",C5="p.t not phone 25 hours",C5="p.t. non-phone 30 hours",C5="full time non-phone",G5=""),0,IF(AND(C5<>"",E5<=TODAY(),F5>=TODAY()),D5,""))

I have assumed you actually wanted to check the strings, if you really want cell references, then replace those strings with the cell references. Please check spelling and string text. I took word for word what you wrote.

Also, I assumed if all statements are false then you want a blank. The last "" in the statement. If you want a 0 or otherwise to be returned if all statements are false then simply replace that "" with your preferred string or value.

Hope this is what you were after..

 

[Geoffrey1231]

Thanks again NBVC....but it doesn't seem to be working. I don't get an error, but the formula only returns a "0", regardless of what's chosen in C5. Anything other than the non-phone choices should return the value that's in D5 (except for a blank). Here's what I've got in I5;

=IF(OR(C5="P.T.non-phone(20hrs)",C5="P.T.non-phone(25hrs)",C5="P.T.non-phone(30hrs)",C5="F.T.non-phone",G5=""),0,IF(AND(C5<>"",E5<=TODAY(),F5>=TODAY(),G5<=TODAY()),D5,0))

Help?

Geoff

 

[NBVC]

Hi Geoff,

Try this one:

=IF(OR(C5="P.T.non-phone(20hrs)",C5="P.T.non-phone(25hrs)",C5="P.T.non-phone(30hrs)",C5="F.T.non-phone",C5="",G5=""),0,IF(AND(E5<=TODAY(),F5>=TODAY(),G5<=TODAY()),D5,""))

so if ANY of these are true: C5 is any of the P.T. Non-phones OR if C5 is blank or if G5 is blank, then a 0 is returned.

If none of those are true and ALL of these are true: E5<=today, F5>=today and G5<=today, then the D5 value is returned.

Otherwise, the cell remains blank.

Hope this helps.

 

[Geoffrey1231]

NBVC...You're going to think I'm testing your sanity. It works well, but I want it to be ok to have cell F5 blank. So I would change your last paragraph to the following;

If none of those are true and ALL of these are true: E5<=today, F5>=today or "" and G5<=today, then the D5 value is returned.

Thanks again,

P.S. Do you have any recommended resources for learning about how to create these types of "IF" statements?


Geoff

 

[NBVC]

Hi Geoff,

I am only to glad to help.

=IF(OR(C5="P.T.non-phone(20hrs)",C5="P.T.non-phone(25hrs)",C5="P.T.non-phone(30hrs)",C5="F.T.non-phone",C5="",G5=""),0,IF(AND(E5<=TODAY(),OR(F5>=TODAY(),F5=""),G5<=TODAY()),D5,""))

If statements are not to difficult to create. It is usually easier to just write down in plain English what tests your are looking for and what to return if your tests evaluate to true and what to do if false, because whatever logic you write down is translated very similarly to an If statement. If you have to check several things, then usually you would group these tests with And or OR statements depending on whether all tests have to be true or any one test can be true. Just look at your last post couple of posts to me and compare them carefully with the statements I provided. In the last post you said you wanted to add the possibility that F5 can be blank ("") as well as being >= today's date. Well I took your statement literally and included those tests in an OR function. So now Excel checks whether 1 or the other holds true.

It is pretty easy to search the web or even this site for good help. There are a lot of books on Excel basics and Advanced.

Here are several sites I found just searching for "If statements" & Excel.

http://www.excel-vba.com/microsoft-excel-logical-formulas.htm

http://www.cpearson.com/excel/excelF.htm

http://www.homeandlearn.co.uk/ME/mes7p1.html

http://www.personal-computer-tutor.com/if1.htm

http://www.personal-computer-tutor.com/if2.htm

Here are some other good sites, that teach/show good tips and formulas

http://www.contextures.com/tiptech.html

http://www.mrexcel.com/articles.shtml#Formulas

http://www.cpearson.com/excel/excelF.htm

Hope I have helped.

Practice plenty. With practice comes perfection.

 

[Geoffrey1231]

Thanks again for everything....the formula worked and I should be all set.

I've learned a bit about formulas and I think the sites you mentioned will help further.


Geoff