Set day and month on date column formula

[naveen327]

Hi,

I have a date-time column [CreatedOn] e.g. 11/08/2010 11:31. I need a formula to extract the year e.g 2010 and then set the day to 01 and the month to 06 and the time to 00:00 and return this as a date-time value.

The formula might need to be two parts extract the year from the CreatedOn column and then set month to 01 and day to 01...

How can I construct the forumla to do this?

Many Thanks for your help,
Nav

 

[VoG]

Try

=DATE(YEAR(A1),6,1)

and format as date plus time.

 

[naveen327]

Thanks very much VoG - super quick response worked perfectly.

Many Thanks
Naveen