multiple "IF" statements in function
 

I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is
what I would ike to use:
If (e) <= (365) Then
(f)= "0 Weeks"
End If
if (e) (365) and <= (730) then
(f) = "1 week"
End If
if (e) (730) and <= (1825) then
(f) = "2 Weeks"
End If
if (e) (1825) and <= (3650) then
(f) = "3 Weeks"
End If
If (e) (3650) Then
(f) = "4 Weeks"
End If
(e) and (f) are the columns
Thanks in advance
Rob

multiple "IF" statements in function
 

replace first 4 Endif with Else

"RRyan" wrote:

I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is
what I would ike to use:
If (e) <= (365) Then
(f)= "0 Weeks"
End If
if (e) (365) and <= (730) then
(f) = "1 week"
End If
if (e) (730) and <= (1825) then
(f) = "2 Weeks"
End If
if (e) (1825) and <= (3650) then
(f) = "3 Weeks"
End If
If (e) (3650) Then
(f) = "4 Weeks"
End If
(e) and (f) are the columns
Thanks in advance
Rob

multiple "IF" statements in function
 

This should work
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E1:E10")) Is Nothing Then
With Target
Select Case .Value
Case Is <= 365
.Offset(0, 1).Value = "0 Weeks"
Case Is <= 730
.Offset(0, 1).Value = "1 Weeks"
Case Is <= 1825
.Offset(0, 1).Value = "2 Weeks"
Case Is <= 3650
.Offset(0, 1).Value = "3 Weeks"
Case Is 3650
.Offset(0, 1).Value = "4 Weeks"
End Select
End With
End If
End Sub

"RRyan" wrote:

I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is
what I would ike to use:
If (e) <= (365) Then
(f)= "0 Weeks"
End If
if (e) (365) and <= (730) then
(f) = "1 week"
End If
if (e) (730) and <= (1825) then
(f) = "2 Weeks"
End If
if (e) (1825) and <= (3650) then
(f) = "3 Weeks"
End If
If (e) (3650) Then
(f) = "4 Weeks"
End If
(e) and (f) are the columns
Thanks in advance
Rob

multiple "IF" statements in function
 

I tried to put your suggestion into the function line in Excel with no luck.
I opened the VB editor and put in the folowing:
If col(e) <= (365) Then
col(f) = "0 weeks"
Else
if col(e) (360) and <= (720) then
col(f) = "1 Week"
Else
if col (e) (720) and <= (1800) then
col(f) = "2 Weeks"
Else
if col (e) (1800) and <= (3600) then
col(f) = "3 Weeks"
Else
If col(e) (3600) Then
col(f) = "4 Weeks"
I get an error on the "<=" and I'm wondering if col(e) is the right way to
assign the function to colmn E

"Atif" wrote:

replace first 4 Endif with Else

"RRyan" wrote:

I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is
what I would ike to use:
If (e) <= (365) Then
(f)= "0 Weeks"
End If
if (e) (365) and <= (730) then
(f) = "1 week"
End If
if (e) (730) and <= (1825) then
(f) = "2 Weeks"
End If
if (e) (1825) and <= (3650) then
(f) = "3 Weeks"
End If
If (e) (3650) Then
(f) = "4 Weeks"
End If
(e) and (f) are the columns
Thanks in advance
Rob

multiple "IF" statements in function
 

put this into a standered Module
and use this formula in cell F1 =VactionTime(E1)
Function VactionTime(target As Range) As String
With target
Select Case .value
Case Is = 0
VactionTime = ""
Case Is <= 365
VactionTime = "0 Weeks"
Case Is <= 730
VactionTime = "1 Weeks"
Case Is <= 1825
VactionTime = "2 Weeks"
Case Is <= 3650
VactionTime = "3 Weeks"
Case Is 3650
VactionTime = "4 Weeks"
End Select
End With
End Function

"RRyan" wrote:

I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is
what I would ike to use:
If (e) <= (365) Then
(f)= "0 Weeks"
End If
if (e) (365) and <= (730) then
(f) = "1 week"
End If
if (e) (730) and <= (1825) then
(f) = "2 Weeks"
End If
if (e) (1825) and <= (3650) then
(f) = "3 Weeks"
End If
If (e) (3650) Then
(f) = "4 Weeks"
End If
(e) and (f) are the columns
Thanks in advance
Rob

multiple "IF" statements in function
 

Try using a lookup table instead.
ColA ColB ColC
1 365 0 weeks
366 730 1 week
731 1825 2 weeks
1826 3650 3 weeks
3650 999999 4 weeks

(Cell A100) (Cell B100) (Cell C100)
Employee Days Employee Weeks of Vaca
AA 739 =vlookup(B100,$A$1:$C$5,3,False)

"RRyan" wrote:

I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is
what I would ike to use:
If (e) <= (365) Then
(f)= "0 Weeks"
End If
if (e) (365) and <= (730) then
(f) = "1 week"
End If
if (e) (730) and <= (1825) then
(f) = "2 Weeks"
End If
if (e) (1825) and <= (3650) then
(f) = "3 Weeks"
End If
If (e) (3650) Then
(f) = "4 Weeks"
End If
(e) and (f) are the columns
Thanks in advance
Rob

multiple "IF" statements in function
 

I got it to work with this.
=IF(D5<360,"0",IF(D5<720,"5",IF(D5<1800,"10",IF(D5 <3600,"15",IF(D5=3600,"20","")))))

"RussellT" wrote:

Try using a lookup table instead.
ColA ColB ColC
1 365 0 weeks
366 730 1 week
731 1825 2 weeks
1826 3650 3 weeks
3650 999999 4 weeks

(Cell A100) (Cell B100) (Cell C100)
Employee Days Employee Weeks of Vaca
AA 739 =vlookup(B100,$A$1:$C$5,3,False)

"RRyan" wrote:

I'm trying to build a vacation tracker but i don't know VB. Is there a way
to get muliple if statements using the function built into excel? This is
what I would ike to use:
If (e) <= (365) Then
(f)= "0 Weeks"
End If
if (e) (365) and <= (730) then
(f) = "1 week"
End If
if (e) (730) and <= (1825) then
(f) = "2 Weeks"
End If
if (e) (1825) and <= (3650) then
(f) = "3 Weeks"
End If
If (e) (3650) Then
(f) = "4 Weeks"
End If
(e) and (f) are the columns
Thanks in advance
Rob