using date function, month shows as January when i type (12)
 

when date of birth is A1 and i use the formula
=date(year(A1)+19,month(12),day(31)), to get the last day of the year before
the 20th birthday, 31/1/1919 appears instead of 31/12/1919. why is this? if
i take the +19 away, the same thing happens. The only time it works is if i
specify the year instead of using another cell.

Does this formula do what you need?
="12/31/"&YEAR(A1)+19

The reason your original formula did not work is because the MONTH()
and DAY() functions require serial date numbers (that is to say, an
Excel date equivalent expressed as a number).

=DATE(YEAR(A1)+19,12,31)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"hsas" wrote in message
...
when date of birth is A1 and i use the formula
=date(year(A1)+19,month(12),day(31)), to get the last day of the year
before
the 20th birthday, 31/1/1919 appears instead of 31/12/1919. why is this?
if
i take the +19 away, the same thing happens. The only time it works is if
i
specify the year instead of using another cell.