Sam has attended:
Excel Dashboards for Business Intelligence course
IF function based on date
Hi,
i need to show either 25% or 27.5% based on whether the date is pre or post 1.6.15 i have the below formula but its not working correctly (below formaula also takes in account available funds so that it cannot show a greater number than is available.)
=IF(H7<1/6/2015,IF((P7-N7-O7)*25%<P7-(N7+O7+Q7+R7),(P7-O7-N7)*25%,P7-(N7+O7+Q7+R7)),IF((P7-N7-O7)*27.5%<P7-(N7+O7+Q7+R7),(P7-O7-N7)*27.5%,P7-(N7+O7+Q7+R7)))
when i use > it shows everything as 25% and when i use < it shows everything as 27.5% it does not seem to take the date in to account?
the date is currently in short date format, i have tried it as number format and it makes no difference.
Can any one help?
RE: IF function based on date
Hi Sam,
Thank you for the forum question.
If you want to test a date you will need the Datevalue function. Please have a look at the If function below. I have a date in A2 I want to test up against 1st June 2015.
=IF(A2>=DATEVALUE("1/6/2015"),27.5%,25%)
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
