• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Identifying active months within a date range, based on start and end date

K

Katya

New Member
Hi again, lovely Excel geeks!

This time I have a name of an employee and his/her employemnt start and leaving dates. I need a formula that will tell me whether an employee was in employment in August 2013. So say somebody's start date is 01/01/2013 and leaving date is 15/08/2013 - I would like the formula to say "yes, worked in August 2013" (or "1"), and if his/her start date is 01/01/2013 and leaving date is 15/05/2013, then formula to say "no, did not work in August 2013" (or "0"). Hope this makes sense... I am having trouble specifically with people that started or left in August, my formula doesn't identify them properly. Thank you in advance!!
Katya
 
Hi Katya, good day.

I assume you just want to check if the employee was working in August 2013 and not the exact date.

Could you please check this formula "=IF(MONTH(A1)&"-"&YEAR(A1)="8-2013","Yes","No")".

Assuming date value is cell A1. Please confirm in case it does not work.
 
I guess you are looking for a formula to match both on the start date & End date. In c1 please enter the last date in august. Assuming the Start date is in A1 & end date is in B1

=IF(AND(C1>A1,C1<B1,"yes, worked in August 2013", "no, did not work in August 2013")
 
=IF(AND(c1>=a1,c1<=b1),"yes, worked in August 2013", "no, did not work in August 2013")
If you want to include 1st & 31st AUG as well, use this formula
 
Hi Katya,

You can use the below in Col.C considering that the End date is in Col.B
=IF(TEXT(B1,"mm/dd/yyyy")<"08/01/2013","Not worked in August","Worked in August")..

You can change the date in the formula to 07/31/2013 if you want to exclude 08/01/2013 as well..

Hope this helps...:)
 
HI guys, so, reading through your equally valid and informative responses, I realised that it's not just my excel skills, but also my logic that was utterly flawed! Thank you so much for setting my thinking straight! As usual, you are all brilliant, and I had a thick day....
Best, Katya
 
@Abhijeet R. Joshi!
Hi!
Your formula doesn't work. It neither considers the year nor the end date.
Regards!
 
@Abhijeet R. Joshi!
Hi!
I actually didn't try it, just analyzed it.
If B1 has a date that:
a) is of months from January to July of years greater than 2013 (not today, but what about next year?)
b) is of months months from September to December of years lesser than 2013 and with leaving dates after the tested value
c) is of any month of years lesser than 2013 and with leaving dates after the tested value
then your formula will return the wrong value.
Regards!
 
You must log in or register to reply here.
Back
Top