Hello
I have a date that is calculated based on several varying conditions. I have started to build a nested function using if/and/or but could not get the result I needed. (Problem in Chair Not in Computer )
I have attached a sample sheet with the conditions I need to incorporate to calculate the Date for Date b
Thanks again for your assistance
How would this work? There is no date in Date1, so what are you expecting the result to be???If date A has data and Date 1 is empty then date b should be Date 1+90
Please double check ALL your requirements to make sure that they are actually possible before anyone works on this for you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Not logicalIf date A has data and Date 1 is empty then date b should be Date 1+90
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
imo, the formula might be
check condition of END result firs, then seperate each condition
and + them together like....
=IF( AND(B2 > 0, C2 > 0, D2 > 0 ) , "END" ,
IF( AND( A2 > 0, B2 > 0, C2 > 0, D2 > 0) , Today() , 0 ) +
IF( A2 - 90 < B2 , B2 + 21 , 0 ) + .... )
may can avoid nested if.
Regards.
My English is very poor, so please be patient >_<"
My Dynamic Related Dropdown post:
https://www.excelforum.com/excel-for...ml#post5657376
https://www.excelforum.com/excel-for...ml#post5655711
https://www.excelforum.com/excel-for...ml#post5655762
My apologies, that should read;
If Date a has data and Date 1 is empty then Date b should be Date a+90
Can date 2 or date 3 actually contain data if date 1 is empty? I cannot help thinking that several of these rules are surplus to requirements. I'd assume that dates 1 to 3 wuld be filled in sequesntially.
Hi
Date 2 and Date 3 can contain data if date 1 is empty. Normally the dates are sequential but every now and again any one of Date 1, 2 or 3 might be missing.
Cheers
Hello,
I have modified my sample to remove Date A, as it introduced too many variables. I have updated the sheet with a visual check that I have included a formula for each potential scenario. (see revised attachment)
Am I looking at this the wrong way and is there other functions that will give the result I'm looking for?
Thanks
Cell E7 reads: IF Date 1 and 3 have data and Date 2 is empty, then Date B should return "END"
The part of the formula that seems to correspond to E7 is: +IF(AND(a2>0,c2>0,ISBLANK(b2),(B2+90)
It seems that should read: +IF(AND(a2>0,c2>0,ISBLANK(b2),"END"
If that is the case then it seems that all instances in which cell C2>0 will yield "END" in cell D2
After that if only A2>0 then D2 should yield A2+90 and both other possibilities result in B2+90.
Therefore try:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMC
Thankyou for your reply, much neater than what I was trying to do!
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks