If & And formula with multiple condition

[MaunishP]

HI Team,

I am looking for formula to with multiple condition to find out real bucket.

Also i want to if end date is there then it should calculate end date minus hire date, IF termination date is not there then it should calculate today date minus hire date./

I have attached excel sheet.

Regards,
Maunish Patel

 

[vletm]

MaunishP
Before continue ...
If 'Bucket'-values are 30, 60, 90, 120, 180
... which 'Bucket' it would belong?

 

[MaunishP]

Hi,

Just modified condition criteria

Bucket
<= 30
> 30 - <=60
> 60 - < =90
> 90 - < =120
> 120 - <= 180
> 180

Also I am looking for In days formula.

Regards,
Maunish Patel

 

[AliGW]

Set up a lookup table (see what I have done in your attachment) and then use this:

=VLOOKUP(IF(C2="",TODAY()-A2,C2-A2),$G$2:$H$7,2,1)

Lookup table:

0 < 30
30 > 30 - < 60
60 > 60 - < 90
90 > 90 - < 120
120 > 120 - < 180
180 > 180

This on its own will return the day numbers:

=IF(C2="",TODAY()-A2,C2-A2)

 

[vletm]

MaunishP
Today is only once.
There were also 'In Days'-column.
How about this?

 

[MaunishP]

Thanks a ton. This is what i was looking for

 

[AliGW]

Which one of us are you addressing?