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

Nested IF Statments with Dates

P

philip_s

New Member
Hey all


I'm working with some mind mumbling "If's" atm, I have collum (A) as the esteminated date, (C) as the ready date,(B) as the caounter between collum(A&C). Formula I use in (B) is: =IF(ISNUMBER(A2);IF(C2="";A2-TODAY();A2-C2);"") as far as good, but in collum (E)I have the real engage date, and in collum (D) gap bettwen collum A&E, and here it's getting nasty. I will have an 'Error' message if collum (E<C) but i can't manage to do it right all the way down. I'll paste formula for you that I have glued together.


=IF(ISNUMBER(A2);IF(A2>=C2;IF(E2="";IF(TODAY()<A2;0;(TODAY()-A2));IF(E2<A2;0;(E2-A2)));"Error");"")


It dosn't work right, I'll get 'Error' message when collum (E) is blank :/


cheers o/
 
of course it's an Error! you have set the formula up to be a false statement to begin with.


When you cut out that section

=IF(TODAY() < A2;0;(TODAY()-A2)) ; IF(E2 < A2;0;(E2-A2))) and put it in another cell you will get a FALSE. And since it is NOT a number, the whole formula looks at the other argument applicable, and that's "ERROR".


You need to clean up this section.
 
Thanks for the fast reply Fred


I was staring me blind, after all began adding more collums until the equation, and I probably lost track.


I will return as soon as I got little sleep:)


cheers
 
hi philip_s, i think you need to define the scenarios on a piece of paper. Describe and encompass all the scenarios you can think of, then write the formula down. At this date and age, a piece of paper and pencil still help me out greatly with my train of thought. ;)
 
Ditto to what Fred said. I actually just did that myself in an attempt to solve your formula...I think there are 5 outputs. Does this formula work for you?


=IF(AND(ISNUMBER(A2);A2>=C2);IF(E2="";IF(TODAY()<A2;0;TODAY()-A2);IF(E2<A2;"Error";E2-A2));"")
 
Hey guys


Just looked at you'r formula Luke, and after a little tweak I've got my desired Error.


here is the formula:


=IF(ISNUMBER(A2);IF(E2="";IF(TODAY()<A2;0;TODAY()-A2);IF(E2<C2;"Error";E2-A2));"")


Next desired step is to convert the negative counter into positive 'where C2<A2 and E2<A2', I will use it as a buffer.


I will post it if i'll get it before you guys do, but I doubt it will happen :D


cheers o/
 
Hey again o/


Regarding Fred's posting.

I must admit that I do not feel I got there to where I can use pencil, but I can certainly see your point, and from your point of view, some things are more obvious than for example for me. I can see it now:)


I'm just sometimes too stupid and too lazy to do it all myself, I prefer to learn from the best;)


cheers :)
 
You must log in or register to reply here.
Back
Top