Kay has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course
IF Statement
Hi,
I'm hoping someone can help me here. I'm trying to produce a basic action log spreadsheet and I want to add due dates. In the cell alongside due date I want the status to automatically appear with the following logic:
if Cell F4 is less or equal to G4 then I want it to read Yes else return “No”
If Cell G4 is blank then I want it to leave it blank
I have created this where F4 is the due date and G4 the completion date
=IF(ISBLANK(G4),"",IF(G4<=F4,"Yes","No"))
However now I am trying to add another scenario which include F4>Today() and G is blank then I want it to also return “Overdue” - IF (F4>TODAY(), G4 =”” - Overdue
I'm having troubles bringing this part into the formula and would appreciate your help to point me in the right direction
Thanks
RE: IF Statement
Hi Kay
What you're looking for is a nested IF statement that includes the AND function.
I've pasted what I think should resolve your issue below.
=IF(F4="","",IF(G4>F4,"Yes",IF(AND(F4<TODAY(),G4=""),"Overdue","No")))
The first part should leave the your result blank if the due date is blank, then if the completion date is later than
the due date to mark it "YES". Finally if the due date is later than today and the completion date hasn't been entered then to mark it off as overdue.
Hope that helps
Kind regards
Wendy
RE: IF Statement
Hi Wendy
Thanks for taking the time to look at my question
The above statement does not resolve my issue hence I have provided an example and sent it across to info@stl-training.co.uk to help understand what I am looking for.
Hope this helps to illustrate my request more clearly
Many thanks
Kay
RE: IF Statement
Hi Kay
There were a few errors in your cell references. I have corrected them and reattached the file.
Kind regards
Wendy
Attached files...
Nested IF statement.xlsxRE: IF Statement
Hi Wendy
Thanks so much for your prompt reply.
I don't appear to have received the attached file in my mailbox. Please can you try re-sending it to me as a reply to my email, many thanks Kay
RE: IF Statement
Hi Wendy
Thanks for helping with this
New to IF statements so please bear with me.
=IF(C4="","",IF(B4>=C4,"Yes",IF(AND(B4<TODAY(),B4=""),"Overdue","No")))
where B4 is the Due date and C4 is the Completion date.
in my if statement if the Due date (Cell B) would need to be before the completion date (Cell C) to mark it as YES so the first part of the statement is working correctly for me
however, the second part where the due date has passed than today and the completion date hasn't been entered then mark this as overdue. I am not able to get this with the equation below as this is returning blank
IF(AND(B4<TODAY(),B4=""),"Overdue","No")))
I can see the issue is around the due date as my due date (cell B4) is auto populated with a date and will never be blank
For example - if the Due date is 21 Jul 2017 (cell B4) and Completion date (cell C4) is (blank) - this is returning blank cell with the above equation however I am looking for this to return OVERDUE -
Is there a solution as to how this can be set to return OVERDUE?
Hope that helps to understand my issue
Many Thanks
Kay
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
