=IF(B6=" ";" ";DATE(YEAR(B6)-1;MONTH(B6)-0;1))

But maybe you mean

=IF(B6="";"";DATE(YEAR(B6)-1;MONTH(B6)-0;1))

In both cases, format as dd-mmm-yyyy

--
Kind regards,

Niek Otten

"Box666" wrote in message
ups.com...
In cell B6 i have a date in the format dd-mmm-yyyy, when I use the
following formula whilst it gives the correct answer ... when I use the
answer in a further formula it is not recognised.

I am guesing that one is a numeric and the other is an alpha answer. (
I have also noticed that the item in B6 sits to the right of the cell,
while the formula answer sits to the left of its cell.)

=IF(B6=" ";" ";(TEXT(DATE(YEAR(B6)-1;MONTH(B6)-0;1);"dd-mmm-yyyy")))


How do I change the above formula so it ends up as a numeric date
answer, but still in the format of dd-mmm-yyyy.