Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel andif and datedif

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel And,IF and Datedif

Excel And,IF and Datedif

ResolvedVersion 2010

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

Mon 9 Oct 2017: Automatically marked as resolved.

Excel tip:

Checking if a calculation adheres to Order of Precedence

When writing formulas you must make sure that results will be calculated as you intended.

Excel adheres to the standard order of precedence for calculations. It calculates percentages, exponents, multiplication, and division in this order before calculating addition and subtraction.

For example, =7+5*3 results in an answer of 22, not 36.

To force a calculation to be completed before another calculations, place the section in parentheses: =(7+5)*3 will result in 36.

To check how excel is evaluating a formula, click on the cell and select the 'Tools' menu, select 'Formula Auditing' and click 'Evaluate Formula'

In the dialog box click on 'Evaluate' to watch as each part of the formula is successively calculated.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.