Can anyone please advise on the following:

A spreadsheet I use identifies specific years in columns A
& B. Is there a simple way of changing the date from just
the years ( say 1995 & 2000) to the first and last dates
in the years in columns A & B . Effectively the years
would become 01/01/1995 in column A and 31/12/2000 in
column B. The years will obviously vary but I need the
first and last dates of the years in columns A & B

What I'm trying to do is limit my keying as the
spreadsheet is filtered at a later date and the majority
of info isn't needed at this point so a quick automatic
way of entering dates in the DD/MM/YYYY format would be
welcome

Many thanks for any help you can give

Frank, use two empty columns (used as temporary, then you can copy paste
special values on top of A and B), format as date and type in the
following two formulae (example for row 1):

=DATEVALUE("01/01/" & A1)
=DATEVALUE("31/12/" & B1)

HTH,
Nikos

"Frank" wrote in message
...
Can anyone please advise on the following:

A spreadsheet I use identifies specific years in columns A
& B. Is there a simple way of changing the date from just
the years ( say 1995 & 2000) to the first and last dates
in the years in columns A & B . Effectively the years
would become 01/01/1995 in column A and 31/12/2000 in
column B. The years will obviously vary but I need the
first and last dates of the years in columns A & B

What I'm trying to do is limit my keying as the
spreadsheet is filtered at a later date and the majority
of info isn't needed at this point so a quick automatic
way of entering dates in the DD/MM/YYYY format would be
welcome

Many thanks for any help you can give

Hi
use the formulas
=DATE(YEAR(A1),1,1)
and
=DATE(YEAR(B1),12,31)

in some helper columns (e.g. C + D). Copy them down and copy the values
and insert the conten as values to clear the formulas


--
Regards
Frank Kabel
Frankfurt, Germany


Frank wrote:
Can anyone please advise on the following:

A spreadsheet I use identifies specific years in columns A
& B. Is there a simple way of changing the date from just
the years ( say 1995 & 2000) to the first and last dates
in the years in columns A & B . Effectively the years
would become 01/01/1995 in column A and 31/12/2000 in
column B. The years will obviously vary but I need the
first and last dates of the years in columns A & B

What I'm trying to do is limit my keying as the
spreadsheet is filtered at a later date and the majority
of info isn't needed at this point so a quick automatic
way of entering dates in the DD/MM/YYYY format would be
welcome

Many thanks for any help you can give

Frank, use two empty columns (used as temporary, then you can copy paste
special values on top of A and B), format as date and type in the
following two formulae (example for row 1):

=DATEVALUE("01/01/" & A1)
=DATEVALUE("31/12/" & B1)

HTH,
Nikos

"Frank" wrote in message
...
Can anyone please advise on the following:

A spreadsheet I use identifies specific years in columns A
& B. Is there a simple way of changing the date from just
the years ( say 1995 & 2000) to the first and last dates
in the years in columns A & B . Effectively the years
would become 01/01/1995 in column A and 31/12/2000 in
column B. The years will obviously vary but I need the
first and last dates of the years in columns A & B

What I'm trying to do is limit my keying as the
spreadsheet is filtered at a later date and the majority
of info isn't needed at this point so a quick automatic
way of entering dates in the DD/MM/YYYY format would be
welcome

Many thanks for any help you can give