Convert complete date to year

[EHS]

Hello I have the following question:

I have a collumn with dates (f.e. 02-12-2004 & 03-05-2005 & 08-08-2005) which I want to convert in two different ways.

First I want to convert it to just a year, so f.e. 2004 (or 1) & 2005 (or 2) & 2005 (or 2). I have tried doing this through 'cell properties --> date --> year'. But the problem is that if I copy the cell (to another programm), I get the full-date again, and not just the year. Is there a formula that can extract only the year from a date?

Second, I want to convert the date to semi-annual dates. So the above dates would be for example: 2004:2 (or 1), 2005:1 (or 2), 2005:2 (or 3).

Third, I have the following question: Is it also possible to choose the 'split-up' of the year (for the second way) yourself? That for example, months April 2004 untill September 2004 are coded as 1, October 2004 untill March 2005 is coded 2, and April 2005 until September 2005 is coded 3?

Thank you very much for your help!
Regards,
Eduard

[Dav]

if your dates are dates use year(a1) to get the year. I am not usre what you mean by semi annual dates

If you mean the first 6 months are :1 and the second 6 months are :2

try

=IF(MONTH(A1)<7,YEAR(A1)&":1",YEAR(A1)&":2")

and perhaps

=TRUNC((YEAR(A1)*12+MONTH(A1)-24046)/6)

Regards

Dav

[Bryan Hessey]

Hello I have the following question:

I have a collumn with dates (f.e. 02-12-2004 & 03-05-2005 & 08-08-2005) which I want to convert in two different ways.

First I want to convert it to just a year, so f.e. 2004 (or 1) & 2005 (or 2) & 2005 (or 2). I have tried doing this through 'cell properties --> date --> year'. But the problem is that if I copy the cell (to another programm), I get the full-date again, and not just the year. Is there a formula that can extract only the year from a date?

Second, I want to convert the date to semi-annual dates. So the above dates would be for example: 2004:2 (or 1), 2005:1 (or 2), 2005:2 (or 3).

Third, I have the following question: Is it also possible to choose the 'split-up' of the year (for the second way) yourself? That for example, months April 2004 untill September 2004 are coded as 1, October 2004 untill March 2005 is coded 2, and April 2005 until September 2005 is coded 3?

Thank you very much for your help!
Regards,
Eduard

Hi,

I think http://www.cpearson.com/excel/datetime.htm has the explanation that is easiest to understand, after which you will be able to extract Year(A1) etc

hth
---

[EHS]

Thanks, I will check the site inmediately!

Regards,

Eduard

[Bryan Hessey]

Thanks, I will check the site inmediately!

Regards,

Eduard

Good, it will help you to understand that the 'date' is the integer number in a cell, currently around the 32000 mark, and that all other forms are Display purposes only.

You can, as mentioned by Dav, extract the Year, and the Month and use these separately, and following Dav's examples set the 'first' and 'second' portion of the year.

Good luck with this.
---