98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » IF Statements
IF Statements
Resolved · Medium Priority · Version 2007 
Featured
Thu 7 Mar 2013, 10:21Delegate Victoria said...
Victoria has attended:
PowerPoint Intermediate Advanced course
IF Statements
Im trying to set up a rag status on positions that have exceeded a certain number of working days.
For example: I use a vacancy tracker which logs new positions, the date they are received and it is manually updated as it progresses to interviews & offers etc. I want to be able to flag roles that are not filled within 25 days of being received - How do I do this? I can't get the if formula right!
Many thanks,
Thu 7 Mar 2013, 12:35Trainer Gary said...
RE: IF Statements
Hi Victoria
Thanks for getting in touch.
Here's a sample of how you could achieve this. Assuming your date is in A1:
=IF((TODAY()-A1)>25,"Yes","No")
The formula will output "Yes" or "No" for overdue or not. It takes today's date and subtracts the required date. If the answer is over 25, say "yes".
Give it a go and let me know if that works for you.
Kind regards
Gary Fenn
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
Thu 7 Mar 2013, 12:47Delegate Victoria said...
RE: IF Statements
Thanks Gary - this is perfect!
Just one further question....
How do I make yes go a certain colour and no a different colour?
Many thanks,
Victoria
Thu 7 Mar 2013, 13:29Trainer Gary said...
RE: IF Statements
Hi Victoria
Glad it worked! To format yes and no differently, use Conditional Formatting. Highlight your column of yes and no values, then on the Home tab select Conditional Formatting.
Choose Highlight Cells Rules > Text that contains... on the left type Yes, and on the right choose the formatting that you would like. Repeat the process for No.
Kind regards
Gary Fenn
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
Thu 7 Mar 2013, 13:32Delegate Victoria said...
RE: IF Statements
Thank you - I just found coditional formatting then I remembered what I was meant to be doing!
Thank you for your swift responses, much appreciated!
Thu 7 Mar 2013, 13:59Delegate Victoria said...
RE: IF Statements
Sorry - one further question.
Can I make Excel calculate the number of working days since something has been live for.
I have used =TODAY()-A2 which calculates total days but I wondered if it will do just working days?
Many thanks,
Victoria
Thu 7 Mar 2013, 14:06Trainer Gary said...
RE: IF Statements
Hi Victoria
Use NETWORKDAYS:
=NETWORKDAYS(A2, TODAY())
Which will find out the number of working days between the contents of A2 and today. Note that public holidays are not included in this, but there are ways to adapt it if you consult the Excel help.
Kind regards
Gary Fenn
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
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Enter formulae into multiple cellsIf a formula is to be used in a series of cells, select the cells first. Now type in your formula and hold the Ctrl key while you press Enter. This enters the formula in each selected cell. |
