Date looks funny!
 

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

=IF(G10,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
------------------------------------------------------------------------
ajames's Profile: http://www.excelforum.com/member.php...o&userid=31502
View this thread: http://www.excelforum.com/showthread...hreadid=514368

Date looks funny!
 

IF(G10,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(G10,YEAR(G1),"")" into

IF(G130000,YEAR(G1),G1)


--
weavtennis
------------------------------------------------------------------------
weavtennis's Profile: http://www.excelforum.com/member.php...fo&userid=3634
View this thread: http://www.excelforum.com/showthread...hreadid=514368

Date looks funny!
 

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(G10,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!

Date looks funny!
 

A big thank-you, it works!


--
ajames
------------------------------------------------------------------------
ajames's Profile: http://www.excelforum.com/member.php...o&userid=31502
View this thread: http://www.excelforum.com/showthread...hreadid=514368