• 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.

Formula IF Function between two dates

Jadee

New Member
Hi. Can you please help me. I already have the IF formula where i want to generate a specific value from a column if between two dates.
Example:
Column A1 Start Date, Column B1 End Date, Column C1 Headcount
Data in 2nd row: Start date Jan. 1, 2014 up to Jan.31, 2015, headcount of 7.
FOrmula in Column D2:
=IF(AND(D1>=A2, D1 <= B2, C2, "")

The formula is working fine but the problem is when there is blank or no date in End date column, no value is returned. I would like to return the value in C2 whether there is end date or not. I would like to make use of one formula only that will work whether the End date has data or not.

Your help is very much appreciated. Thanks
 

vletm

Excel Ninja
Jadee
Your sent ... D2-formula ... have to have a typo = it won't work.
Upload a sample Excel-file to see Your formula which work - which should modify.
Enclose enough data too.
 

AndiLovesExcel

New Member
The presented formula will return a value if there's no end date and your D1 date lies before Start Date. If you don't want that to happen, switch around AND and OR as follows:

=IF(AND(D1>=A2,OR(D1<=B2, B2="")),C2,"")

Regards
 
Top