excel - Date + 6 months?

Question

I am looking for a formula that will change the cell if the date is greater than 6 months ago. e.g If I enter 1st June I need the cell highlighted from the following 1st December onwards.

Answer 1

Use the Datedif function.

Answer 2

Go to:

Tools > Add-Ins...

Then make sure that Analysis ToolPak (should be the first item) is checked. Then click OK.

The Analysis Toolpak isn't normally added in by default, so you need to check that it is added or otherwise the EDATE function won't work.

Now in some cell that won't get changed, enter the formula:

=EDATE(NOW(), -6)

Afterwards you may want to format the cell as a date if the cell looks like a number.

Go to:

Format > Cells...

Click the Number tab, then select Date from the list and pick the date format you want then click OK.

After formatting that one cell now you are going to format the ones you want highlighted.

Go to:

Format > Conditional Formatting...

For the Window that pops up you want for the areas:

1.) Cell Value Is
2.) less than

Then in the third entry, click the little button to the right of the text entry box and select the cell you entered the EDATE formula into.

Now click on the Format... button then click the Patterns tab. Then you'll select the color you want. Then click OK on each window. It will highlight the cell that color if the cell occurred before the date in the cell where you entered the EDATE formula.

Answer 3

This is one way of doing that.

=EDATE (E3,6)

Where

E3 = your original date = 6/1/07