Calculating and adding dates + n

Please read details in attached document
sample.xls
propolisAsked:
Who is Participating?
 
redmondbConnect With a Mentor Commented:
Propolis,

Assuming 29/02/2012, please see attached. Formula is...
=IF(OR(MID(C3,1,15)<>"contract date +",RIGHT(C3,7)<>" months"),"",EDATE($D$2,MID(C3,17,LEN(C3)-16-7)*1))

Regards,
Brian.
sample-V2.xls
0
 
jppintoCommented:
Assuming you put your initial date on G3 cell, you can use a formula like this:

=DATE(YEAR(G3),MONTH(G3)+8,DAY(G3))

to add 8 months to the initial date.
0
 
jppintoCommented:
Then you can use Conditional Formatting to change the fill color, like this:

=IF(($E$3-$D$3)>7,TRUE,FALSE)  - For Red fill
=IF(AND(($E$3-$D$3)<7,($E$3-$D$3)>0),TRUE,FALSE) - For Amber fill

Please check the attached example.

jppinto
sample.xls
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
pritamduttCommented:
As desired Please see attached spreadsheet.

Hope this helps
sample.xlsx
0
 
redmondbCommented:
Hi, propolis,

What should the Target Date be when the Contact Date is 31/1/2012 and the number of months is one? 29/02/2012? 02/03/2012?

Thanks,
Brian.
0
 
redmondbCommented:
Thanks, propolis!
0
 
pritamduttCommented:
Hi, propolis

May I know the limitations you faced with the solution I proposed?

Thanks!
0
 
jppintoCommented:
propolis, was there something wrong with my solution also?
0
All Courses

From novice to tech pro — start learning today.