Date formula question

Hi

I"m trying to get the formula below to work but I just get "#VALUE" written as a return when A1 refers to a cell that has a date entered (date formatted).

=DATE(YEAR(A1),MONTH(A1)+6,DAY(A1))

I am a novice at this

What am I doing wrong??

I couldn't get the formula to fail unless I used a non-date text in the field (even typing in August 1 as text didn't return an error).

Can you show what is actually in the date cell?

That formula is valid. If it gives a #VALUE! error that means A1 doesn't contain a date (or even a number).

If A1 looks like a date then it's probably formatted as text or contains some spaces.

Try using "Text to Columns" to convert

Select column with date(s)

Data > Text to columns > Next > Next > at step 3 select "date" from "column data format" and required date type e.g. DMY. Finish

Hey it works now thank you.

My problem was that the dates in A1 weren't being recognised because I was inputting them as 3.07.07 instead of 3/07/07 and excel was "seeing them as text".

Next question, for those that still have patience for me:

How do I get the formula below to reject returns that arise when J3 or K3 is blank?

=DATE(YEAR(K3), MONTH(K3), DAY(K3)+(J3*7))

Many thanks

Rather than

=DATE(YEAR(K3), MONTH(K3), DAY(K3)+(J3*7))

it's easier to use

=K3+J3*7

To return a blank if either K3 or J3 is blank try

=IF(OR(K3="",J3=""),"",K3+J3*7)

Thanks muchly. That's done the trick!