If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 IF and IF and IF
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

IF and IF and IF

#1
August 9th 12, 12:45 PM
 Ramona88 Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 3
IF and IF and IF

Hi there,

I need a formula which should be quite straight forward (in my head) but if I do it the way I think it should work it comes back with an error. I hope you can help me

I have the following fields:
Name
Start Date
Eligible (this should be a formula)
Today's Date

Criteria:
Employees that have been with the company for less than 1 year -> return 0
Employees that have been with the company between 1 and 2 years -> return 3
Employees that have been with the company for 2+ years -> return 5

Is that do-able?

Many thanks,
Ramona
#2
August 9th 12, 03:47 PM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster Posts: 929
IF and IF and IF

Hi Ramona,

Am Thu, 9 Aug 2012 11:45:33 +0000 schrieb Ramona88:

> I have the following fields:
> Name
> Start Date
> Eligible (this should be a formula)
> Today's Date

you don't need a column for today's date.
If Name is in A and Start Date in B, then try:
=VLOOKUP(DATEDIF(B2,TODAY(),"y"),{0,0;1,3;2,5},2,1 )

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
#3
August 10th 12, 07:31 AM posted to microsoft.public.excel.worksheet.functions
 Stan Brown external usenet poster Posts: 508
IF and IF and IF

On Thu, 9 Aug 2012 11:45:33 +0000, Ramona88 wrote:
>
> Criteria:
> Employees that have been with the company for less than 1 year -> return
> 0
> Employees that have been with the company between 1 and 2 years ->
> return 3
> Employees that have been with the company for 2+ years -> return 5
>
> Is that do-able?

Of course it is. What formula did you try?

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
Shikata ga nai...
#4
August 10th 12, 12:47 PM
 Ramona88 Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 3

@Claus Busch
I tried your formula but it doesn't give me the correct output. Thank you anyway.

@Stan Brown
I tried this but don't really know how to connect those two IFs so it obviously gives me an error:

=IF(D4<=\$F\$1-365,2.5,0)AND(IF(D4>\$F\$1-730,5,2.5))

D4 -> Start Date
F1 -> Today's Date
#5
August 10th 12, 04:15 PM posted to microsoft.public.excel.worksheet.functions
 Claus Busch external usenet poster Posts: 929
IF and IF and IF

Hi Ramona,

Am Fri, 10 Aug 2012 11:47:23 +0000 schrieb Ramona88:

> @Claus Busch
> I tried your formula but it doesn't give me the correct output. Thank
> you anyway.

have another try:
=VLOOKUP(DATEDIF(B2,TODAY(),"y"),{0.0,1.3,2.5},2,1 )

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
#6
August 11th 12, 07:03 AM posted to microsoft.public.excel.worksheet.functions
 Stan Brown external usenet poster Posts: 508
IF and IF and IF

On Fri, 10 Aug 2012 11:47:23 +0000, Ramona88 wrote:
>
> @Claus Busch
> I tried your formula but it doesn't give me the correct output. Thank
> you anyway.

What's with the @ signs? Please quote correctly:

> @Stan Brown
> I tried this but don't really know how to connect those two IFs so it
> obviously gives me an error:
>
> =IF(D4<=\$F\$1-365,2.5,0)AND(IF(D4>\$F\$1-730,5,2.5))
>
> D4 -> Start Date
> F1 -> Today's Date

(And from your previous article, which you should have quoted but
didn't):

> Employees that have been with the company for less than 1 year ->
> return 0
> Employees that have been with the company between 1 and 2 years ->
> return 3
> Employees that have been with the company for 2+ years -> return 5

Think about what IF does: if a condition is true, return A; otherwise
return B. If you want to test multiple conditions, you have in
effect an if-then-else-if situation.

=IF(A1=1, "one", IF(A1=2, "two", IF(A1=3, "three", "none of the
above) ) )

See the pattern?

=IF(F1-D4<365, 1, IF(F1-D4<2*365, 3, 5) )

We're both ignoring leap years, by the way.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
Shikata ga nai...
#7
September 24th 12, 05:37 PM
 Ramona88 Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 3

Hi Stan,

I tried your formula but it still doesn't work. It always gives back "3" for everything under 2 years.
Instead of the "<2*365" we need to say ">=365 and <2*365" but again not sure how to do that.

Please can you give it another try for me?

I really appreciate it!

Ramona
#8
September 28th 12, 04:21 AM posted to microsoft.public.excel.worksheet.functions
 Stan Brown external usenet poster Posts: 508
IF and IF and IF

On Mon, 24 Sep 2012 16:37:58 +0000, Ramona88 wrote:
>
> Hi Stan,
>
> Sorry about the wrong quoting.
>
> I tried your formula but it still doesn't work. It always gives back "3"
> for everything under 2 years.
> Instead of the "<2*365" we need to say ">=365 and <2*365" but again not
> sure how to do that.
>
> Please can you give it another try for me?

Since you didn't quote anything from what you were responding to, I
have no idea what you're talking about.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
Shikata ga nai...

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

All times are GMT +1. The time now is 02:37 PM.