Conditional Formatting: Calculate Date One Year Later From Another Date

**Kym2101** · 05-01-2012, 12:53 AM

Hi,

I have two columns; column L contains a date that a report has been received and column P needs to contain the date one year from the date entered in column L.

Does anyone know the conditional formatting, so that when I enter a date in column L, column P will auto populate with the date one year later?

I hope that makes sense.

kind Regards

**grizzly6969** · 05-01-2012, 01:20 AM

format column L and P date then in P1

Please Login or Register  to view this content.

and copy down

this will work for 12 months --- not sure if it will take into account for leap year

**Kym2101** · 05-01-2012, 02:02 AM

Hi,

Sorry I tried your formula but it didn't work. It came up with #NAME?. Is there a formula that will apply it to the whole column as the list is very long and new rows will keep being added.

Kind Regards

**Fotis1991** · 05-01-2012, 02:23 AM

Hi

grizzly6969's formula is one of the ways to do this and works fine.

Where do you use this formula? In Conditional Format rules, OR in P1??

**Kym2101** · 05-01-2012, 02:28 AM

Hi,

I used that formula in P2 as P1 contains a title for the column but I changed the number to L2 in the formula. I tried it again and it still comes up as the error. I have also tried it in conditional formatting but nothing happens.

Also I forgot to mention before that if there is no date entered into column L then the corresponding cell for coloumn P needs to stay blank.

Kind Regards

**Fotis1991** · 05-01-2012, 02:36 AM

Hi

I can not understand what are you doing wrong.

=IF(L2="";"";EDATE(L2;12))

This formula( maybe comma and not semi colons) must work for you in P2 and copy down.

Take a look to the example.

**grizzly6969** · 05-01-2012, 02:45 AM

have you formatted the cells as Date ---- 3/14/01

when you enter date in L2 -- enter it 1/1/12 and see if this makes a differance

**grizzly6969** · 05-01-2012, 03:08 AM

try this

Please Login or Register  to view this content.

and format your cells

then click on the cell and drag it down as far as you need

**Kym2101** · 05-01-2012, 09:24 PM

Hi,

Sorry it still isn't working. I have formatted that columns to date format and then applied the formula and copied it down but it still comes up with that name error.

Kind Regards

**Kym2101** · 05-01-2012, 09:52 PM

Hi,

I tweaked the above formulas and this one seemed to work:

<code>
=IF(L2="","",L2+365.25)
</code>

Thank you for your help. I think my spreadsheet is just a bit touchy.