IF, DATEDIF statements and Conditional formatting problem

[trianna]

Hello everyone,


I have learned so much helpful information from this site, blog and question boards that I feel embarrassed to have to ask for help again.


Here is the issue.


I have two columns that contain the Start Date and End Date of an event. These columns are the basis of a number of IF, DATEDIF statements and Conditional formatting within a user completed scheduling template.


Due to a recent format request, (the request comes after I've learned so much from these boards that they feel it's easy ) the Start Date and End Date column fields must contain the 00/00/00 until a user enters a date. Besides supporting additional helper columns that determine event length when dates are entered, the Start Date and End Date column 00/00/00 is used in other IF statements and Conditional formatting to identify user input errors and incorrect information. An IF Statement example would be =IF($R1<>"NA","00/00/00") and CF example, =IF($R1<>"00/00/00",AND($AA1="00/00/00",AND($AB1<>"R",AND($AB1<>"C")))).


Everything works well except the initial DATEDIF function which is =DATEDIF(R1,S1,"D")+1. The function obviously works fine when dates are entered in the Start Date and End Date column fields. When the text 00/00/00 the #VALUE! Error is received as expected.


I’ve tried to write a nested IF statement to overcome the text entry and focus on the dates so many different ways I can’t even remember where I started.


This is an example of one =IF($R1="00/00/00","0",AND($S1="00/00/00","0",AND(DATEDIF(R1,S1,"D")+1))) It will change the field entry to 0 (zero) but will not work correctly when actual dates are entered.


Does anyone have a thought on how to resolve this?


Thanks,


Trianna

 

[Luke M]

Hello!

Learning about XL "can" become a curse. People start expecting impossible things to be "easy" because you helped on that one thing...but I wander.


First, a point on the AND function. You can string all the criteria you want to check within a single function. Or, perhaps you really need an OR function. As it returns True/False, no need for IF function with either of these. Thus, your CF formula might be something like:

=OR($R1<>"NA",$AA1="00/00/00",$AB1="R",$AB1="C")

Just something to think about.


Anyway, for the IF/DATEDIF problem. Since your looking for 2 dates, aka 2 numbers, perhaps this will do the trick for you:

=IF(COUNT($R1:$S1)<>2,0,DATEDIF(R1,S1,"D")+1)

 

[trianna]

Luke M,


Thank you for the understanding on the EASY... I think the curse part comes to each of us when we realize that once we have learned a little, we need/crave to learn more.

I will deeper in to the COUNT and OR function. I like to keep the formulas as trim as possible.


The =IF(COUNT($R1:$S1)<>2,0,DATEDIF(R1,S1,"D")+1) formula is great, it works well on 3 of the four helper columns.


This is what happens.


Column R - Event Start Date - R5 – Cell pre formatted with 00/00/00 until user enters actual Event Start Date.

Column S - Event End Date - S5 – Cell pre formatted with 00/00/00 until user enters actual Event End Date.


Column T - Total Event Days - = IF(COUNT($R6:$S6)<>2,0,DATEDIF(R6,S6,"D")+1). Cell equals 0 (zero) with 00/00/00 entries, works correctly with user entered dates.


Column U - Start Month Event Days - =SUM(T5-W5). Cell equals 0 (zero) with 00/00/00 entries, works correctly with user entered dates


Column V - Start Month Days left - =DATE(YEAR(R5),MONTH(R5)+1,1)-R5. Cell equals #VALUE! with 00/00/00 entries, works correctly with user entered dates.


Column W - End Month Event Days - =IF(SUM(T5-V5)<0,0,SUM(T5-V5)). Cell equals 0 (zero) with 00/00/00 entries, works correctly with user entered dates.


I hope it makes sense how I've laid it out.


Thanks,


Trianna

 

[Luke M]

Col T:

= IF(COUNT($R5:$S5)<>2,0,DATEDIF(R5,S5,"D")+1)

Col U:

=IF(T5,T5-W5,0)

Col V:

=IF(T5,DATE(YEAR(R5),MONTH(R5)+1,1)-R5,0)

Col W:

=IF(T5,MAX(0,T5-V5),0)


The latter 3 formulas check against col T. If T = 0 (which is equivalent to FALSE), then there are not enough user entered dates, and all formulas evaluate to 0. Other than that, I just simplified the last formula a bit.

Does this work?