Changing dates entered in 2005 to previous years

On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
wrote:

>I have a three spreadsheets. Each represents a different year and each has
>one column with different dates within that year.
>Unfortunately I entered all of these dates in the default date format ie
>7-jan and did not enter a year. I now need to ensure the year is present in
>the date formula in some way ie 07/01/04 or any format that shows the year.
>Is there a quick way of changing this rather than retyping all.


It takes longer to type than to do.
First make a backup of your original data.

Assume your dates are in A2:An

1. In some unused column, row 2, let us say cell AA2, enter one of these
formulas:

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
(MONTH(DATE(YEAR(A2)-1,MONTH(A2),
DAY(A2)))<>MONTH(A2))

or, if you have the analysis tool pak installed:

=EDATE(A2,-12)

The above will subtract one year (and adjust for leap year, if necessary).

To subtract more than one year, in formula one change the '1' to the number of
years you wish to subtract; or in formula 2 multiply the '12' by the number of
years you wish to subtract.

2. Copy/drag the formula down to AAn.

3. With AA2:AAn selected:
Edit/Copy

4. Select cell A2
5. Edit/Paste Special Values


--ron

sorry should have said "all the 2004 dates default to 2005" ...
--
lucy


"lucy" wrote:

> Eek - feeling a bit stupid here - maths never being my strong point! OK I've
> changed the format and as you say all the 2004 dates default to 2004. How do
> I get the whole column to subtract the contents of each cell by 365?
> Extremely grateful if you can help...
> --
> lucy
>
>
> "John Michl" wrote:
>
> > To determine the year of the date, change the format (Format | Cells |
> > Number ) to one of the formats that shows the date. If you created the
> > dates this year, they would have defaulted to 2005. Since dates are
> > really just serial numbers, you can do simple math with them like
> > subtracting 365 to get the same date as last year. If you expect a
> > leap year to cause problems, you can use some of the Date functions to
> > extract the month and day from the serial number and combine it with
> > the appropriate year.
> >
> > Hope that helps. Let me know if you need more details.
> >
> > - John Michl
> >
> >

Eek - feeling a bit stupid here - maths never being my strong point! OK I've
changed the format and as you say all the 2004 dates default to 2004. How do
I get the whole column to subtract the contents of each cell by 365?
Extremely grateful if you can help...
--
lucy


"John Michl" wrote:

> To determine the year of the date, change the format (Format | Cells |
> Number ) to one of the formats that shows the date. If you created the
> dates this year, they would have defaulted to 2005. Since dates are
> really just serial numbers, you can do simple math with them like
> subtracting 365 to get the same date as last year. If you expect a
> leap year to cause problems, you can use some of the Date functions to
> extract the month and day from the serial number and combine it with
> the appropriate year.
>
> Hope that helps. Let me know if you need more details.
>
> - John Michl
>
>

To determine the year of the date, change the format (Format | Cells |
Number ) to one of the formats that shows the date. If you created the
dates this year, they would have defaulted to 2005. Since dates are
really just serial numbers, you can do simple math with them like
subtracting 365 to get the same date as last year. If you expect a
leap year to cause problems, you can use some of the Date functions to
extract the month and day from the serial number and combine it with
the appropriate year.

Hope that helps. Let me know if you need more details.

- John Michl

On Fri, 26 Aug 2005 13:28:03 -0700, "lucy" <[email protected]>
wrote:

>bingo!!!! Fab!!!! No retyping for me!!! Very Happy and unbelievably
>grateful!!! First time I've ever used this site. Thank you, thank you thank
>you :-)
>--
>lucy

You're welcome. And thanks for the enthusiastic feedback!


--ron

Thanks for replying. I think I may not have explained the problem well
enough. I've entered dates into all three spreadsheets (2002,2003,2004) in
the last two weeks, so when I change the format from 7-jan to one that
includes the year, it automatically assumes (not surprisingly) it's a date
that in year 2005. So I now need to go back to everything that's in the 2004
spreadsheet for example that currently says 7-jan or even 07/01/05 and ensure
that it somehow says 2004 at the end. Not fussy about which format though if
that's of any help.... Hope this explains it better. Dreading the thought
of a long retype!


--
lucy


"bj" wrote:

> try changing the date format in the cell. It may have automatically formated
> with this years date.
>
> If it did not You might try adding 38352 to all of the date values then
> check whether it gives you the correct date.
> "lucy" wrote:
>
> > I have a three spreadsheets. Each represents a different year and each has
> > one column with different dates within that year.
> > Unfortunately I entered all of these dates in the default date format ie
> > 7-jan and did not enter a year. I now need to ensure the year is present in
> > the date formula in some way ie 07/01/04 or any format that shows the year.
> > Is there a quick way of changing this rather than retyping all.
> > --
> > lucy

try changing the date format in the cell. It may have automatically formated
with this years date.

If it did not You might try adding 38352 to all of the date values then
check whether it gives you the correct date.
"lucy" wrote:

> I have a three spreadsheets. Each represents a different year and each has
> one column with different dates within that year.
> Unfortunately I entered all of these dates in the default date format ie
> 7-jan and did not enter a year. I now need to ensure the year is present in
> the date formula in some way ie 07/01/04 or any format that shows the year.
> Is there a quick way of changing this rather than retyping all.
> --
> lucy

sorry should have said "all the 2004 dates default to 2005" ...
--
lucy


"lucy" wrote:

> Eek - feeling a bit stupid here - maths never being my strong point! OK I've
> changed the format and as you say all the 2004 dates default to 2004. How do
> I get the whole column to subtract the contents of each cell by 365?
> Extremely grateful if you can help...
> --
> lucy
>
>
> "John Michl" wrote:
>
> > To determine the year of the date, change the format (Format | Cells |
> > Number ) to one of the formats that shows the date. If you created the
> > dates this year, they would have defaulted to 2005. Since dates are
> > really just serial numbers, you can do simple math with them like
> > subtracting 365 to get the same date as last year. If you expect a
> > leap year to cause problems, you can use some of the Date functions to
> > extract the month and day from the serial number and combine it with
> > the appropriate year.
> >
> > Hope that helps. Let me know if you need more details.
> >
> > - John Michl
> >
> >

To determine the year of the date, change the format (Format | Cells |
Number ) to one of the formats that shows the date. If you created the
dates this year, they would have defaulted to 2005. Since dates are
really just serial numbers, you can do simple math with them like
subtracting 365 to get the same date as last year. If you expect a
leap year to cause problems, you can use some of the Date functions to
extract the month and day from the serial number and combine it with
the appropriate year.

Hope that helps. Let me know if you need more details.

- John Michl

try changing the date format in the cell. It may have automatically formated
with this years date.

If it did not You might try adding 38352 to all of the date values then
check whether it gives you the correct date.
"lucy" wrote:

> I have a three spreadsheets. Each represents a different year and each has
> one column with different dates within that year.
> Unfortunately I entered all of these dates in the default date format ie
> 7-jan and did not enter a year. I now need to ensure the year is present in
> the date formula in some way ie 07/01/04 or any format that shows the year.
> Is there a quick way of changing this rather than retyping all.
> --
> lucy

Thanks for replying. I think I may not have explained the problem well
enough. I've entered dates into all three spreadsheets (2002,2003,2004) in
the last two weeks, so when I change the format from 7-jan to one that
includes the year, it automatically assumes (not surprisingly) it's a date
that in year 2005. So I now need to go back to everything that's in the 2004
spreadsheet for example that currently says 7-jan or even 07/01/05 and ensure
that it somehow says 2004 at the end. Not fussy about which format though if
that's of any help.... Hope this explains it better. Dreading the thought
of a long retype!


--
lucy


"bj" wrote:

> try changing the date format in the cell. It may have automatically formated
> with this years date.
>
> If it did not You might try adding 38352 to all of the date values then
> check whether it gives you the correct date.
> "lucy" wrote:
>
> > I have a three spreadsheets. Each represents a different year and each has
> > one column with different dates within that year.
> > Unfortunately I entered all of these dates in the default date format ie
> > 7-jan and did not enter a year. I now need to ensure the year is present in
> > the date formula in some way ie 07/01/04 or any format that shows the year.
> > Is there a quick way of changing this rather than retyping all.
> > --
> > lucy

Eek - feeling a bit stupid here - maths never being my strong point! OK I've
changed the format and as you say all the 2004 dates default to 2004. How do
I get the whole column to subtract the contents of each cell by 365?
Extremely grateful if you can help...
--
lucy


"John Michl" wrote:

> To determine the year of the date, change the format (Format | Cells |
> Number ) to one of the formats that shows the date. If you created the
> dates this year, they would have defaulted to 2005. Since dates are
> really just serial numbers, you can do simple math with them like
> subtracting 365 to get the same date as last year. If you expect a
> leap year to cause problems, you can use some of the Date functions to
> extract the month and day from the serial number and combine it with
> the appropriate year.
>
> Hope that helps. Let me know if you need more details.
>
> - John Michl
>
>

try changing the date format in the cell. It may have automatically formated
with this years date.

If it did not You might try adding 38352 to all of the date values then
check whether it gives you the correct date.
"lucy" wrote:

> I have a three spreadsheets. Each represents a different year and each has
> one column with different dates within that year.
> Unfortunately I entered all of these dates in the default date format ie
> 7-jan and did not enter a year. I now need to ensure the year is present in
> the date formula in some way ie 07/01/04 or any format that shows the year.
> Is there a quick way of changing this rather than retyping all.
> --
> lucy

Thanks for replying. I think I may not have explained the problem well
enough. I've entered dates into all three spreadsheets (2002,2003,2004) in
the last two weeks, so when I change the format from 7-jan to one that
includes the year, it automatically assumes (not surprisingly) it's a date
that in year 2005. So I now need to go back to everything that's in the 2004
spreadsheet for example that currently says 7-jan or even 07/01/05 and ensure
that it somehow says 2004 at the end. Not fussy about which format though if
that's of any help.... Hope this explains it better. Dreading the thought
of a long retype!


--
lucy


"bj" wrote:

> try changing the date format in the cell. It may have automatically formated
> with this years date.
>
> If it did not You might try adding 38352 to all of the date values then
> check whether it gives you the correct date.
> "lucy" wrote:
>
> > I have a three spreadsheets. Each represents a different year and each has
> > one column with different dates within that year.
> > Unfortunately I entered all of these dates in the default date format ie
> > 7-jan and did not enter a year. I now need to ensure the year is present in
> > the date formula in some way ie 07/01/04 or any format that shows the year.
> > Is there a quick way of changing this rather than retyping all.
> > --
> > lucy

On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
wrote:

>I have a three spreadsheets. Each represents a different year and each has
>one column with different dates within that year.
>Unfortunately I entered all of these dates in the default date format ie
>7-jan and did not enter a year. I now need to ensure the year is present in
>the date formula in some way ie 07/01/04 or any format that shows the year.
>Is there a quick way of changing this rather than retyping all.


It takes longer to type than to do.
First make a backup of your original data.

Assume your dates are in A2:An

1. In some unused column, row 2, let us say cell AA2, enter one of these
formulas:

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
(MONTH(DATE(YEAR(A2)-1,MONTH(A2),
DAY(A2)))<>MONTH(A2))

or, if you have the analysis tool pak installed:

=EDATE(A2,-12)

The above will subtract one year (and adjust for leap year, if necessary).

To subtract more than one year, in formula one change the '1' to the number of
years you wish to subtract; or in formula 2 multiply the '12' by the number of
years you wish to subtract.

2. Copy/drag the formula down to AAn.

3. With AA2:AAn selected:
Edit/Copy

4. Select cell A2
5. Edit/Paste Special Values


--ron

bingo!!!! Fab!!!! No retyping for me!!! Very Happy and unbelievably
grateful!!! First time I've ever used this site. Thank you, thank you thank
you :-)
--
lucy


"Ron Rosenfeld" wrote:

> On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
> wrote:
>
> >I have a three spreadsheets. Each represents a different year and each has
> >one column with different dates within that year.
> >Unfortunately I entered all of these dates in the default date format ie
> >7-jan and did not enter a year. I now need to ensure the year is present in
> >the date formula in some way ie 07/01/04 or any format that shows the year.
> >Is there a quick way of changing this rather than retyping all.
>
>
> It takes longer to type than to do.
> First make a backup of your original data.
>
> Assume your dates are in A2:An
>
> 1. In some unused column, row 2, let us say cell AA2, enter one of these
> formulas:
>
> =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
> (MONTH(DATE(YEAR(A2)-1,MONTH(A2),
> DAY(A2)))<>MONTH(A2))
>
> or, if you have the analysis tool pak installed:
>
> =EDATE(A2,-12)
>
> The above will subtract one year (and adjust for leap year, if necessary).
>
> To subtract more than one year, in formula one change the '1' to the number of
> years you wish to subtract; or in formula 2 multiply the '12' by the number of
> years you wish to subtract.
>
> 2. Copy/drag the formula down to AAn.
>
> 3. With AA2:AAn selected:
> Edit/Copy
>
> 4. Select cell A2
> 5. Edit/Paste Special Values
>
>
> --ron
>

On Fri, 26 Aug 2005 13:28:03 -0700, "lucy" <[email protected]>
wrote:

>bingo!!!! Fab!!!! No retyping for me!!! Very Happy and unbelievably
>grateful!!! First time I've ever used this site. Thank you, thank you thank
>you :-)
>--
>lucy

You're welcome. And thanks for the enthusiastic feedback!


--ron

Eek - feeling a bit stupid here - maths never being my strong point! OK I've
changed the format and as you say all the 2004 dates default to 2004. How do
I get the whole column to subtract the contents of each cell by 365?
Extremely grateful if you can help...
--
lucy


"John Michl" wrote:

> To determine the year of the date, change the format (Format | Cells |
> Number ) to one of the formats that shows the date. If you created the
> dates this year, they would have defaulted to 2005. Since dates are
> really just serial numbers, you can do simple math with them like
> subtracting 365 to get the same date as last year. If you expect a
> leap year to cause problems, you can use some of the Date functions to
> extract the month and day from the serial number and combine it with
> the appropriate year.
>
> Hope that helps. Let me know if you need more details.
>
> - John Michl
>
>

To determine the year of the date, change the format (Format | Cells |
Number ) to one of the formats that shows the date. If you created the
dates this year, they would have defaulted to 2005. Since dates are
really just serial numbers, you can do simple math with them like
subtracting 365 to get the same date as last year. If you expect a
leap year to cause problems, you can use some of the Date functions to
extract the month and day from the serial number and combine it with
the appropriate year.

Hope that helps. Let me know if you need more details.

- John Michl

sorry should have said "all the 2004 dates default to 2005" ...
--
lucy


"lucy" wrote:

> Eek - feeling a bit stupid here - maths never being my strong point! OK I've
> changed the format and as you say all the 2004 dates default to 2004. How do
> I get the whole column to subtract the contents of each cell by 365?
> Extremely grateful if you can help...
> --
> lucy
>
>
> "John Michl" wrote:
>
> > To determine the year of the date, change the format (Format | Cells |
> > Number ) to one of the formats that shows the date. If you created the
> > dates this year, they would have defaulted to 2005. Since dates are
> > really just serial numbers, you can do simple math with them like
> > subtracting 365 to get the same date as last year. If you expect a
> > leap year to cause problems, you can use some of the Date functions to
> > extract the month and day from the serial number and combine it with
> > the appropriate year.
> >
> > Hope that helps. Let me know if you need more details.
> >
> > - John Michl
> >
> >

try changing the date format in the cell. It may have automatically formated
with this years date.

If it did not You might try adding 38352 to all of the date values then
check whether it gives you the correct date.
"lucy" wrote:

> I have a three spreadsheets. Each represents a different year and each has
> one column with different dates within that year.
> Unfortunately I entered all of these dates in the default date format ie
> 7-jan and did not enter a year. I now need to ensure the year is present in
> the date formula in some way ie 07/01/04 or any format that shows the year.
> Is there a quick way of changing this rather than retyping all.
> --
> lucy

On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
wrote:

>I have a three spreadsheets. Each represents a different year and each has
>one column with different dates within that year.
>Unfortunately I entered all of these dates in the default date format ie
>7-jan and did not enter a year. I now need to ensure the year is present in
>the date formula in some way ie 07/01/04 or any format that shows the year.
>Is there a quick way of changing this rather than retyping all.


It takes longer to type than to do.
First make a backup of your original data.

Assume your dates are in A2:An

1. In some unused column, row 2, let us say cell AA2, enter one of these
formulas:

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
(MONTH(DATE(YEAR(A2)-1,MONTH(A2),
DAY(A2)))<>MONTH(A2))

or, if you have the analysis tool pak installed:

=EDATE(A2,-12)

The above will subtract one year (and adjust for leap year, if necessary).

To subtract more than one year, in formula one change the '1' to the number of
years you wish to subtract; or in formula 2 multiply the '12' by the number of
years you wish to subtract.

2. Copy/drag the formula down to AAn.

3. With AA2:AAn selected:
Edit/Copy

4. Select cell A2
5. Edit/Paste Special Values


--ron

To determine the year of the date, change the format (Format | Cells |
Number ) to one of the formats that shows the date. If you created the
dates this year, they would have defaulted to 2005. Since dates are
really just serial numbers, you can do simple math with them like
subtracting 365 to get the same date as last year. If you expect a
leap year to cause problems, you can use some of the Date functions to
extract the month and day from the serial number and combine it with
the appropriate year.

Hope that helps. Let me know if you need more details.

- John Michl

sorry should have said "all the 2004 dates default to 2005" ...
--
lucy


"lucy" wrote:

> Eek - feeling a bit stupid here - maths never being my strong point! OK I've
> changed the format and as you say all the 2004 dates default to 2004. How do
> I get the whole column to subtract the contents of each cell by 365?
> Extremely grateful if you can help...
> --
> lucy
>
>
> "John Michl" wrote:
>
> > To determine the year of the date, change the format (Format | Cells |
> > Number ) to one of the formats that shows the date. If you created the
> > dates this year, they would have defaulted to 2005. Since dates are
> > really just serial numbers, you can do simple math with them like
> > subtracting 365 to get the same date as last year. If you expect a
> > leap year to cause problems, you can use some of the Date functions to
> > extract the month and day from the serial number and combine it with
> > the appropriate year.
> >
> > Hope that helps. Let me know if you need more details.
> >
> > - John Michl
> >
> >

bingo!!!! Fab!!!! No retyping for me!!! Very Happy and unbelievably
grateful!!! First time I've ever used this site. Thank you, thank you thank
you :-)
--
lucy


"Ron Rosenfeld" wrote:

> On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
> wrote:
>
> >I have a three spreadsheets. Each represents a different year and each has
> >one column with different dates within that year.
> >Unfortunately I entered all of these dates in the default date format ie
> >7-jan and did not enter a year. I now need to ensure the year is present in
> >the date formula in some way ie 07/01/04 or any format that shows the year.
> >Is there a quick way of changing this rather than retyping all.
>
>
> It takes longer to type than to do.
> First make a backup of your original data.
>
> Assume your dates are in A2:An
>
> 1. In some unused column, row 2, let us say cell AA2, enter one of these
> formulas:
>
> =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
> (MONTH(DATE(YEAR(A2)-1,MONTH(A2),
> DAY(A2)))<>MONTH(A2))
>
> or, if you have the analysis tool pak installed:
>
> =EDATE(A2,-12)
>
> The above will subtract one year (and adjust for leap year, if necessary).
>
> To subtract more than one year, in formula one change the '1' to the number of
> years you wish to subtract; or in formula 2 multiply the '12' by the number of
> years you wish to subtract.
>
> 2. Copy/drag the formula down to AAn.
>
> 3. With AA2:AAn selected:
> Edit/Copy
>
> 4. Select cell A2
> 5. Edit/Paste Special Values
>
>
> --ron
>

Eek - feeling a bit stupid here - maths never being my strong point! OK I've
changed the format and as you say all the 2004 dates default to 2004. How do
I get the whole column to subtract the contents of each cell by 365?
Extremely grateful if you can help...
--
lucy


"John Michl" wrote:

> To determine the year of the date, change the format (Format | Cells |
> Number ) to one of the formats that shows the date. If you created the
> dates this year, they would have defaulted to 2005. Since dates are
> really just serial numbers, you can do simple math with them like
> subtracting 365 to get the same date as last year. If you expect a
> leap year to cause problems, you can use some of the Date functions to
> extract the month and day from the serial number and combine it with
> the appropriate year.
>
> Hope that helps. Let me know if you need more details.
>
> - John Michl
>
>

On Fri, 26 Aug 2005 13:28:03 -0700, "lucy" <[email protected]>
wrote:

>bingo!!!! Fab!!!! No retyping for me!!! Very Happy and unbelievably
>grateful!!! First time I've ever used this site. Thank you, thank you thank
>you :-)
>--
>lucy

You're welcome. And thanks for the enthusiastic feedback!


--ron

Thanks for replying. I think I may not have explained the problem well
enough. I've entered dates into all three spreadsheets (2002,2003,2004) in
the last two weeks, so when I change the format from 7-jan to one that
includes the year, it automatically assumes (not surprisingly) it's a date
that in year 2005. So I now need to go back to everything that's in the 2004
spreadsheet for example that currently says 7-jan or even 07/01/05 and ensure
that it somehow says 2004 at the end. Not fussy about which format though if
that's of any help.... Hope this explains it better. Dreading the thought
of a long retype!


--
lucy


"bj" wrote:

> try changing the date format in the cell. It may have automatically formated
> with this years date.
>
> If it did not You might try adding 38352 to all of the date values then
> check whether it gives you the correct date.
> "lucy" wrote:
>
> > I have a three spreadsheets. Each represents a different year and each has
> > one column with different dates within that year.
> > Unfortunately I entered all of these dates in the default date format ie
> > 7-jan and did not enter a year. I now need to ensure the year is present in
> > the date formula in some way ie 07/01/04 or any format that shows the year.
> > Is there a quick way of changing this rather than retyping all.
> > --
> > lucy

bingo!!!! Fab!!!! No retyping for me!!! Very Happy and unbelievably
grateful!!! First time I've ever used this site. Thank you, thank you thank
you :-)
--
lucy


"Ron Rosenfeld" wrote:

> On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
> wrote:
>
> >I have a three spreadsheets. Each represents a different year and each has
> >one column with different dates within that year.
> >Unfortunately I entered all of these dates in the default date format ie
> >7-jan and did not enter a year. I now need to ensure the year is present in
> >the date formula in some way ie 07/01/04 or any format that shows the year.
> >Is there a quick way of changing this rather than retyping all.
>
>
> It takes longer to type than to do.
> First make a backup of your original data.
>
> Assume your dates are in A2:An
>
> 1. In some unused column, row 2, let us say cell AA2, enter one of these
> formulas:
>
> =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
> (MONTH(DATE(YEAR(A2)-1,MONTH(A2),
> DAY(A2)))<>MONTH(A2))
>
> or, if you have the analysis tool pak installed:
>
> =EDATE(A2,-12)
>
> The above will subtract one year (and adjust for leap year, if necessary).
>
> To subtract more than one year, in formula one change the '1' to the number of
> years you wish to subtract; or in formula 2 multiply the '12' by the number of
> years you wish to subtract.
>
> 2. Copy/drag the formula down to AAn.
>
> 3. With AA2:AAn selected:
> Edit/Copy
>
> 4. Select cell A2
> 5. Edit/Paste Special Values
>
>
> --ron
>

sorry should have said "all the 2004 dates default to 2005" ...
--
lucy


"lucy" wrote:

> Eek - feeling a bit stupid here - maths never being my strong point! OK I've
> changed the format and as you say all the 2004 dates default to 2004. How do
> I get the whole column to subtract the contents of each cell by 365?
> Extremely grateful if you can help...
> --
> lucy
>
>
> "John Michl" wrote:
>
> > To determine the year of the date, change the format (Format | Cells |
> > Number ) to one of the formats that shows the date. If you created the
> > dates this year, they would have defaulted to 2005. Since dates are
> > really just serial numbers, you can do simple math with them like
> > subtracting 365 to get the same date as last year. If you expect a
> > leap year to cause problems, you can use some of the Date functions to
> > extract the month and day from the serial number and combine it with
> > the appropriate year.
> >
> > Hope that helps. Let me know if you need more details.
> >
> > - John Michl
> >
> >

On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
wrote:

>I have a three spreadsheets. Each represents a different year and each has
>one column with different dates within that year.
>Unfortunately I entered all of these dates in the default date format ie
>7-jan and did not enter a year. I now need to ensure the year is present in
>the date formula in some way ie 07/01/04 or any format that shows the year.
>Is there a quick way of changing this rather than retyping all.


It takes longer to type than to do.
First make a backup of your original data.

Assume your dates are in A2:An

1. In some unused column, row 2, let us say cell AA2, enter one of these
formulas:

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
(MONTH(DATE(YEAR(A2)-1,MONTH(A2),
DAY(A2)))<>MONTH(A2))

or, if you have the analysis tool pak installed:

=EDATE(A2,-12)

The above will subtract one year (and adjust for leap year, if necessary).

To subtract more than one year, in formula one change the '1' to the number of
years you wish to subtract; or in formula 2 multiply the '12' by the number of
years you wish to subtract.

2. Copy/drag the formula down to AAn.

3. With AA2:AAn selected:
Edit/Copy

4. Select cell A2
5. Edit/Paste Special Values


--ron

Eek - feeling a bit stupid here - maths never being my strong point! OK I've
changed the format and as you say all the 2004 dates default to 2004. How do
I get the whole column to subtract the contents of each cell by 365?
Extremely grateful if you can help...
--
lucy


"John Michl" wrote:

> To determine the year of the date, change the format (Format | Cells |
> Number ) to one of the formats that shows the date. If you created the
> dates this year, they would have defaulted to 2005. Since dates are
> really just serial numbers, you can do simple math with them like
> subtracting 365 to get the same date as last year. If you expect a
> leap year to cause problems, you can use some of the Date functions to
> extract the month and day from the serial number and combine it with
> the appropriate year.
>
> Hope that helps. Let me know if you need more details.
>
> - John Michl
>
>

Thanks for replying. I think I may not have explained the problem well
enough. I've entered dates into all three spreadsheets (2002,2003,2004) in
the last two weeks, so when I change the format from 7-jan to one that
includes the year, it automatically assumes (not surprisingly) it's a date
that in year 2005. So I now need to go back to everything that's in the 2004
spreadsheet for example that currently says 7-jan or even 07/01/05 and ensure
that it somehow says 2004 at the end. Not fussy about which format though if
that's of any help.... Hope this explains it better. Dreading the thought
of a long retype!


--
lucy


"bj" wrote:

> try changing the date format in the cell. It may have automatically formated
> with this years date.
>
> If it did not You might try adding 38352 to all of the date values then
> check whether it gives you the correct date.
> "lucy" wrote:
>
> > I have a three spreadsheets. Each represents a different year and each has
> > one column with different dates within that year.
> > Unfortunately I entered all of these dates in the default date format ie
> > 7-jan and did not enter a year. I now need to ensure the year is present in
> > the date formula in some way ie 07/01/04 or any format that shows the year.
> > Is there a quick way of changing this rather than retyping all.
> > --
> > lucy

try changing the date format in the cell. It may have automatically formated
with this years date.

If it did not You might try adding 38352 to all of the date values then
check whether it gives you the correct date.
"lucy" wrote:

> I have a three spreadsheets. Each represents a different year and each has
> one column with different dates within that year.
> Unfortunately I entered all of these dates in the default date format ie
> 7-jan and did not enter a year. I now need to ensure the year is present in
> the date formula in some way ie 07/01/04 or any format that shows the year.
> Is there a quick way of changing this rather than retyping all.
> --
> lucy

To determine the year of the date, change the format (Format | Cells |
Number ) to one of the formats that shows the date. If you created the
dates this year, they would have defaulted to 2005. Since dates are
really just serial numbers, you can do simple math with them like
subtracting 365 to get the same date as last year. If you expect a
leap year to cause problems, you can use some of the Date functions to
extract the month and day from the serial number and combine it with
the appropriate year.

Hope that helps. Let me know if you need more details.

- John Michl

On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
wrote:

>I have a three spreadsheets. Each represents a different year and each has
>one column with different dates within that year.
>Unfortunately I entered all of these dates in the default date format ie
>7-jan and did not enter a year. I now need to ensure the year is present in
>the date formula in some way ie 07/01/04 or any format that shows the year.
>Is there a quick way of changing this rather than retyping all.


It takes longer to type than to do.
First make a backup of your original data.

Assume your dates are in A2:An

1. In some unused column, row 2, let us say cell AA2, enter one of these
formulas:

=DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
(MONTH(DATE(YEAR(A2)-1,MONTH(A2),
DAY(A2)))<>MONTH(A2))

or, if you have the analysis tool pak installed:

=EDATE(A2,-12)

The above will subtract one year (and adjust for leap year, if necessary).

To subtract more than one year, in formula one change the '1' to the number of
years you wish to subtract; or in formula 2 multiply the '12' by the number of
years you wish to subtract.

2. Copy/drag the formula down to AAn.

3. With AA2:AAn selected:
Edit/Copy

4. Select cell A2
5. Edit/Paste Special Values


--ron

bingo!!!! Fab!!!! No retyping for me!!! Very Happy and unbelievably
grateful!!! First time I've ever used this site. Thank you, thank you thank
you :-)
--
lucy


"Ron Rosenfeld" wrote:

> On Fri, 26 Aug 2005 12:07:01 -0700, "lucy" <[email protected]>
> wrote:
>
> >I have a three spreadsheets. Each represents a different year and each has
> >one column with different dates within that year.
> >Unfortunately I entered all of these dates in the default date format ie
> >7-jan and did not enter a year. I now need to ensure the year is present in
> >the date formula in some way ie 07/01/04 or any format that shows the year.
> >Is there a quick way of changing this rather than retyping all.
>
>
> It takes longer to type than to do.
> First make a backup of your original data.
>
> Assume your dates are in A2:An
>
> 1. In some unused column, row 2, let us say cell AA2, enter one of these
> formulas:
>
> =DATE(YEAR(A2)-1,MONTH(A2),DAY(A2))-
> (MONTH(DATE(YEAR(A2)-1,MONTH(A2),
> DAY(A2)))<>MONTH(A2))
>
> or, if you have the analysis tool pak installed:
>
> =EDATE(A2,-12)
>
> The above will subtract one year (and adjust for leap year, if necessary).
>
> To subtract more than one year, in formula one change the '1' to the number of
> years you wish to subtract; or in formula 2 multiply the '12' by the number of
> years you wish to subtract.
>
> 2. Copy/drag the formula down to AAn.
>
> 3. With AA2:AAn selected:
> Edit/Copy
>
> 4. Select cell A2
> 5. Edit/Paste Special Values
>
>
> --ron
>

sorry should have said "all the 2004 dates default to 2005" ...
--
lucy


"lucy" wrote:

> Eek - feeling a bit stupid here - maths never being my strong point! OK I've
> changed the format and as you say all the 2004 dates default to 2004. How do
> I get the whole column to subtract the contents of each cell by 365?
> Extremely grateful if you can help...
> --
> lucy
>
>
> "John Michl" wrote:
>
> > To determine the year of the date, change the format (Format | Cells |
> > Number ) to one of the formats that shows the date. If you created the
> > dates this year, they would have defaulted to 2005. Since dates are
> > really just serial numbers, you can do simple math with them like
> > subtracting 365 to get the same date as last year. If you expect a
> > leap year to cause problems, you can use some of the Date functions to
> > extract the month and day from the serial number and combine it with
> > the appropriate year.
> >
> > Hope that helps. Let me know if you need more details.
> >
> > - John Michl
> >
> >

I have a three spreadsheets. Each represents a different year and each has
one column with different dates within that year.
Unfortunately I entered all of these dates in the default date format ie
7-jan and did not enter a year. I now need to ensure the year is present in
the date formula in some way ie 07/01/04 or any format that shows the year.
Is there a quick way of changing this rather than retyping all.
--
lucy

Eek - feeling a bit stupid here - maths never being my strong point! OK I've
changed the format and as you say all the 2004 dates default to 2004. How do
I get the whole column to subtract the contents of each cell by 365?
Extremely grateful if you can help...
--
lucy


"John Michl" wrote:

> To determine the year of the date, change the format (Format | Cells |
> Number ) to one of the formats that shows the date. If you created the
> dates this year, they would have defaulted to 2005. Since dates are
> really just serial numbers, you can do simple math with them like
> subtracting 365 to get the same date as last year. If you expect a
> leap year to cause problems, you can use some of the Date functions to
> extract the month and day from the serial number and combine it with
> the appropriate year.
>
> Hope that helps. Let me know if you need more details.
>
> - John Michl
>
>

Thanks for replying. I think I may not have explained the problem well
enough. I've entered dates into all three spreadsheets (2002,2003,2004) in
the last two weeks, so when I change the format from 7-jan to one that
includes the year, it automatically assumes (not surprisingly) it's a date
that in year 2005. So I now need to go back to everything that's in the 2004
spreadsheet for example that currently says 7-jan or even 07/01/05 and ensure
that it somehow says 2004 at the end. Not fussy about which format though if
that's of any help.... Hope this explains it better. Dreading the thought
of a long retype!


--
lucy


"bj" wrote:

> try changing the date format in the cell. It may have automatically formated
> with this years date.
>
> If it did not You might try adding 38352 to all of the date values then
> check whether it gives you the correct date.
> "lucy" wrote:
>
> > I have a three spreadsheets. Each represents a different year and each has
> > one column with different dates within that year.
> > Unfortunately I entered all of these dates in the default date format ie
> > 7-jan and did not enter a year. I now need to ensure the year is present in
> > the date formula in some way ie 07/01/04 or any format that shows the year.
> > Is there a quick way of changing this rather than retyping all.
> > --
> > lucy

try changing the date format in the cell. It may have automatically formated
with this years date.

If it did not You might try adding 38352 to all of the date values then
check whether it gives you the correct date.
"lucy" wrote:

> I have a three spreadsheets. Each represents a different year and each has
> one column with different dates within that year.
> Unfortunately I entered all of these dates in the default date format ie
> 7-jan and did not enter a year. I now need to ensure the year is present in
> the date formula in some way ie 07/01/04 or any format that shows the year.
> Is there a quick way of changing this rather than retyping all.
> --
> lucy

To determine the year of the date, change the format (Format | Cells |
Number ) to one of the formats that shows the date. If you created the
dates this year, they would have defaulted to 2005. Since dates are
really just serial numbers, you can do simple math with them like
subtracting 365 to get the same date as last year. If you expect a
leap year to cause problems, you can use some of the Date functions to
extract the month and day from the serial number and combine it with
the appropriate year.

Hope that helps. Let me know if you need more details.

- John Michl