June has attended:
Excel Intermediate course
Excel And,IF and Datedif
Excel intermediate course
I would to use a multiple IF statement between two dates (years as 1 whole number).
This is the formula I am using:
=IF(Q84>10,7.5*33,IF(Q84<5,7.5*27,IF(AND(Q84>5,Q84<10),7.5*29,0)))
I need 3 different calculations.
If less than 5
if more than 10
If more than 5 and less than 10
than do the calculations.
Instead of using Q84(which is the number of years from today I got using datedif), I would like to use the datedif with the IF statement.
This is the formula I've done myself
=IF(DATEDIF(Q84,TODAY(),"y")>10,7.5*33,IF(AND(Q84>5,Q84<10),7.5*29,0))
I want the second part of the formula to include the datedif instead of Q84.
Thank you
RE: Excel And,IF and Datedif
Hi June,
Thank you for the forum question.
Please try:
=IF(DATEDIF(Q84,TODAY(),"y")>10,7.5*33,if(DATEDIF(Q84,TODAY(),"y")>=5,7.5*29,0))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Excel And,IF and Datedif
Thank you Jens.
How do I include if more than 5 and less than 10 using the datedif?
the formula has to include:
if less than 5 7.5*27
if more than 10 7.5*33
if more the 5 and less 10 7.5*29
thank you.
RE: Excel And,IF and Datedif
Hi June,
Try:
=IF(DATEDIF(Q84,TODAY(),"y")>10,7.5*33,if(DATEDIF(Q84,TODAY(),"y")>=5,7.5*29,7.5*27))
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector