Few days back we have learned how to find if 2 sets of dates overlap using Excel Formulas. For eg. we have learned that to check whether {1 July,2010 to 23 July 2010} overlaps with the date range {15 July, 2010 to 16 August 2010}, we can use a formula like,
=if(or(and(x>=a,x<=b),and(a>=x,a<=y)), "Overlap","Do not overlap")
where {x,y} are {1 July,2010 to 23 July 2010} and {a,b} are {15 July, 2010 to 16 August 2010}.
Robert, an Excel School student asked a question last week that adds an interesting twist to this problem.
I’m trying to calculate days using several nested IF functions, but I keep getting errors – was hoping you might be able to evaluate my formula and tell me what I’m doing incorrectly:
Here is my framework – I have a start date field which can be any date (let’s call this f6), and an end date field which can be any date (let’s call this g6). I want to calculate the number of days within the start date and end date that fall within the month May 2010.
Essentially Robert wants to find out how may days between {x,y} overlap with {a,b}.
Now, I have already figured out a simple enough solution for it. But it is no fun showing off what I know. So here is the homework for you.
Your homework:
Figure out how to find the number of days in overlap and post your answer in comments (click here).
PS: Today, I am attending a talk by Seth Godin at Hyderabad. I am super excited to finally hear him live. I have been reading his blog for almost 5 years now, and he has been one of the motivating forces for me. So naturally I will be slow to approve comments or reply to mails. Give me a day to get back to you.
3 Responses to “How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial”
Hi Chandoo,
I am responsible for tracking when church reports are submitted on time or not and the variations from the due date for submission.
Here is the Scenario;
The due date for the submission of monthly reports is on the 5th of each month. and I would like to know how many reports have been submitted on time (i.e, those that have been submitted on or before the due date) I would also want to track those reports that have been submitted after the due date has passed.
How can I create such a tracker?
Hi Chandoo,
I am a member of your excel school.
I was trying to create SOP Tracker I follow all your steps but I keep this error below.
The list source must be a delimited list, or a reference to a single row or cell.
I try looking on YouTube for answer but no luck.
can you help on this?
thanks
Carl.
Dear Mr. Chando,
Rakesh, I'm working in a private company in the UAE. Recently, I'm struggling to get more details about the staff sick, annual, unpaid, and leaves. I would like to get a tracker in excel. Could you please help me in this situation?
I also watching your videos in YouTube. i hope you can help me on this situation.