Visual Basic/ Macro date formatting in Excel 97
 

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

Visual Basic/ Macro date formatting in Excel 97
 

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

Visual Basic/ Macro date formatting in Excel 97
 

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

Visual Basic/ Macro date formatting in Excel 97
 

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