Question description:
This user has given permission to use the problem statement for this
blog.
I have a spreadsheet where we track deadlines for questionnaire completions. This formula is currently set up like this: =IF(AND(ISBLANK(L2),ISBLANK(S2)),IF(TODAY()>=(K2),"Closed",IF(TODAY()<(J2),IF(TODAY()<(F2),"Not open","On-time"),"Late")),"Complete")
I would like to add a condition so that if today is two weeks away from the deadline or closer, it shows up as "nearing deadline"
Solved by S. U. in 24 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
31/08/2018 - 08:00
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
31/08/2018 - 08:00
Sorry I'm just seeing i didn't upload the spreadsheet
Excelchat Expert
31/08/2018 - 08:01
That's okay. You can upload the file through our chat. :)
User
31/08/2018 - 08:01
Yup, I just did that
[Uploaded an Excel file]
User
31/08/2018 - 08:01
So, I actually was speaking to someone on your website earlier but i got disconnected before they could fix the issue
Excelchat Expert
31/08/2018 - 08:02
Oh I see. That's too bad, but let's continue fixing it here. :)
User
31/08/2018 - 08:02
but basically, in column n of this spreadsheet
User
31/08/2018 - 08:02
i have a formula that works
User
31/08/2018 - 08:02
that i'm using to track timelines for data collection
Excelchat Expert
31/08/2018 - 08:02
Okay, noted on that.
User
31/08/2018 - 08:02
what i would like is to add another condition
User
31/08/2018 - 08:03
so basically it would be set up so that if today's date is before the date in column f
User
31/08/2018 - 08:03
it shows up as not open
User
31/08/2018 - 08:03
if today's date is between the date in column f and two weeks before the date in column g
User
31/08/2018 - 08:03
it would say "on time"
User
31/08/2018 - 08:04
if it's between two weeks before column g and the date in column g, it would say, "nearing deadline"
User
31/08/2018 - 08:04
anything between column g and column h is late
User
31/08/2018 - 08:04
i'm sorry
User
31/08/2018 - 08:04
i had the columns wrong
User
31/08/2018 - 08:04
i forgot that i had hidden some
User
31/08/2018 - 08:05
it's column j that is the due date
Excelchat Expert
31/08/2018 - 08:05
Okay, noted on that. I've actually been looking at the formula, which is quite understandable.
User
31/08/2018 - 08:05
ok
User
31/08/2018 - 08:05
so the person who was trying to help before
User
31/08/2018 - 08:05
set up the formula in column m
User
31/08/2018 - 08:06
unfortunately, this formula doesn't quite work
User
31/08/2018 - 08:06
it's close, but it doesnt take the year into account i think?
User
31/08/2018 - 08:06
for example
User
31/08/2018 - 08:06
if i change the date in j23 to 9/14/2019
User
31/08/2018 - 08:06
it says NC
User
31/08/2018 - 08:07
which is obviously not the case
User
31/08/2018 - 08:07
that should be "not open"
User
31/08/2018 - 08:07
he used "nc" instead of nearing deadline at first
Excelchat Expert
31/08/2018 - 08:07
Noted on that. Just to clarify, you just want to add the condition that IF the date in column J is two weeks or less from now, it should display "Nearing deadline", correct?
User
31/08/2018 - 08:08
yes exactly
Excelchat Expert
31/08/2018 - 08:08
Noted on that. We can just add it then in your current formula, then we can test it if it works.
User
31/08/2018 - 08:09
ok thank you so much
Excelchat Expert
31/08/2018 - 08:09
Okay, let me work on it for a while and update you the soonest! :)
User
31/08/2018 - 08:09
perfect
Excelchat Expert
31/08/2018 - 08:13
Already done! I modified your original formula in column N to include the said condition you've mentioned. In N23, the status is now Nearing deadline, as required.
[Uploaded an Excel file]
User
31/08/2018 - 08:15
Thank you! let me take a look
User
31/08/2018 - 08:17
ok that looks great!
User
31/08/2018 - 08:17
can you explain to me how you did that
Excelchat Expert
31/08/2018 - 08:17
Sure thing!
User
31/08/2018 - 08:17
when i tried to add in another condition, it kept saying i had too many arguments
User
31/08/2018 - 08:17
also, it took the other person 40 minutes and they still got it wrong
User
31/08/2018 - 08:17
thank you so much for getting it right the first time and so quickly
Excelchat Expert
31/08/2018 - 08:17
Always glad to be of help. :)
Excelchat Expert
31/08/2018 - 08:17
Let me explain how I did it. :)
Excelchat Expert
31/08/2018 - 08:18
So basically, after analyzing your existing formula and your requirement, it seems the simplest and best answer was to enter another IF function in your nested IF statement.
Excelchat Expert
31/08/2018 - 08:19
The formula starts checking the date in column J at "IF(TODAY()<(J23)" part. However, after checking whether this is TRUE or FALSE, it now proceeds in checking the date in column F.
Excelchat Expert
31/08/2018 - 08:20
Before it does this, we need to insert the new IF statement in between to check whether column J is two weeks or less from now. Thus, I added the "IF(J23-TODAY()<=14,"Nearing deadline"" part in between.
Excelchat Expert
31/08/2018 - 08:21
What this does is before it checks out column F, it first checks again column J if it's two weeks or less from now. If TRUE, it returns "Nearing deadline" and ends in there. Otherwise, if FALSE, it now proceeds to checking out column F.
User
31/08/2018 - 08:23
that's amazing! thank you!! i tried to do something similar but it kept saying i had too many arguments for the function
User
31/08/2018 - 08:23
so i know i was doing something wrong, but i'm not quite sure what
Excelchat Expert
31/08/2018 - 08:23
The reason why it shows "You've entered too many arguments" previously was perhaps due to the incorrect placement of brackets and commas in the formula. Somehow, this is really pretty tricky and you would need to know exactly where to put it. In our case, the closing bracket for the newly added IF function is just before the "Late" text in the formula. :)
User
31/08/2018 - 08:24
That is so great. Thank you so much!
User
31/08/2018 - 08:24
I really appreciate it!
Excelchat Expert
31/08/2018 - 08:24
Nice! You can now end the session via the button on your upper right (sorry, can't do it from our end). I would also appreciate it if you can leave a great feedback after this.Thanks so much for using Got it pro and have a nice day! :)
User
31/08/2018 - 08:24
Of course--will do! you too!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
