Solved

# Calculating and adding dates + n

Posted on 2011-10-11
234 Views
Last Modified: 2012-05-12
Please read details in attached document
sample.xls
0
Question by:propolis
8 Comments

LVL 33

Expert Comment

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

LVL 33

Expert Comment

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

LVL 9

Expert Comment

As desired Please see attached spreadsheet.

Hope this helps
sample.xlsx
0

LVL 26

Expert Comment

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

LVL 26

Accepted Solution

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

LVL 26

Expert Comment

Thanks, propolis!
0

LVL 9

Expert Comment

Hi, propolis

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

Thanks!
0

LVL 33

Expert Comment

propolis, was there something wrong with my solution also?
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### 732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!