Date looks funny!

[ajames]

I have a spreadsheet whereby in column G the user inputs a date and column H contains the formula

=IF(G1>0,YEAR(G1),"")

However, I need the user to be able to enter the date in column G as dd/mm/yyyy OR yyyy

Column H needs to display yyyy

This works fine if the user is only able to enter dd/mm/yyyy in column G and this column has been set up to recognise the contents as a Date in that format. However, if the user, for example enters 2008 in column G, then column H reads 1905 - please help!

[weavtennis]

IF(G1>0,YEAR(G1),"")

Excel uses the Windows date convention (day 1 = Jan 1, 1900, Day 2 = Jan 2 1900, ... today (Feb 19, 2006) = 38768.

Try making the "IF(G1>0,YEAR(G1),"")" into

IF(G1>30000,YEAR(G1),G1)

[Roger Govier]

Hi
Try
=IF(G1="","",IF(G1<2100,G1,YEAR(G1)))

--
Regards

Roger Govier



ajames wrote:
> I have a spreadsheet whereby in column G the user inputs a date and
> column H contains the formula
>
> =IF(G1>0,YEAR(G1),"")
>
> However, I need the user to be able to enter the date in column G as
> dd/mm/yyyy OR yyyy
>
> Column H needs to display yyyy
>
> This works fine if the user is only able to enter dd/mm/yyyy in column
> G and this column has been set up to recognise the contents as a Date
> in that format. However, if the user, for example enters 2008 in
> column G, then column H reads 1905 - please help!

[ajames]

A big thank-you, it works!