I am setting up a spreadsheet of investments. The first column (A, row 1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). It takes a long
time to set all that up. I'm now wondering if the dates can be automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change from
2008 to 2009? Can that be done without my having to input all the dates
again for next year? Thanks for any help you can give?

With the date in A1, you can put this formula in A2:

=A1+1

which will add one day on to the previous day. Copy this formula down
and you will have all your dates for the whole year. You only need to
change the date in A1 to have a knock-on effect on the other dates.

Hope this helps.

Pete

On Jan 18, 5:27*pm, Dick C wrote:
I am setting up a spreadsheet of investments. *The first column (A, row 1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). *It takes a long
time to set all that up. *I'm now wondering if the dates can be automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change from
2008 to 2009? *Can that be done without my having to input all the dates
again for next year? *Thanks for any help you can give?

Thanks for ur help Pete. The formula is very good if I don't have the name
of the days in the block or if I include Sat/Sunday. I need to have Monday
01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in
A3 etc. I will only be using Monday through Friday as there are only 5
trading days. So picture all the days and dates in the A column (from
1/21/08 down through 12/31/2008. Then when next Jan arrives I want to change
the dates to line up with the days of the week in 2009. For instance if I
had started this year on Tuesday 01/01/2008, then is there an easy way for me
to click a space to change to 01/01/2009 and have the day be Thursday - which
the first falls on next year? This is difficult to explain I know and I'm
just not sure it can be done. But give it a go and let me know if you think
that it can. If not, it's OK I'll just type it all in again, but it sure
takes a long, long time.

"Pete_UK" wrote:

With the date in A1, you can put this formula in A2:

=A1+1

which will add one day on to the previous day. Copy this formula down
and you will have all your dates for the whole year. You only need to
change the date in A1 to have a knock-on effect on the other dates.

Hope this helps.

Pete

On Jan 18, 5:27 pm, Dick C wrote:
I am setting up a spreadsheet of investments. The first column (A, row 1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). It takes a long
time to set all that up. I'm now wondering if the dates can be automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change from
2008 to 2009? Can that be done without my having to input all the dates
again for next year? Thanks for any help you can give?

Try this formula instead in A2:

=IF(WEEKDAY(A1)=6,A1+3,A1+1)

and then copy it down. This will give you the days in sequence, but
will jump from Friday to the next Monday, so again, all you will need
to do is change the date in A1 to the first working day of the year
and the formulae will do the rest.

Saves all that typing.

Hope this helps.

Pete


On Jan 19, 1:15*am, Dick C wrote:
Thanks for ur help Pete. *The formula is very good if I don't have the name
of the days in the block or if I include Sat/Sunday. *I need to have Monday
01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008 in
A3 etc. *I will only be using Monday through Friday as there are only 5
trading days. *So picture all the days and dates in the A column (from
1/21/08 down through 12/31/2008. *Then when next Jan arrives I want to change
the dates to line up with the days of the week in 2009. *For instance if I
had started this year on Tuesday 01/01/2008, then is there an easy way for me
to click a space to change to 01/01/2009 and have the day be Thursday - which
the first falls on next year? *This is difficult to explain I know and I'm
just not sure it can be done. *But give it a go and let me know if you think
that it can. *If not, it's OK I'll just type it all in again, but it sure
takes a long, long time. *



"Pete_UK" wrote:
With the date in A1, you can put this formula in A2:

=A1+1

which will add one day on to the previous day. Copy this formula down
and you will have all your dates for the whole year. You only need to
change the date in A1 to have a knock-on effect on the other dates.

Hope this helps.

Pete

On Jan 18, 5:27 pm, Dick C wrote:
I am setting up a spreadsheet of investments. *The first column (A, row 1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday 01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). *It takes a long
time to set all that up. *I'm now wondering if the dates can be automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change from
2008 to 2009? *Can that be done without my having to input all the dates
again for next year? *Thanks for any help you can give?- Hide quoted text -

- Show quoted text -

Dick,

Have just re-read your post. If you need to see both the day and the date in
the cell, then you can do this with formatting - highlight column A, then
click on Format | Cells | Number tab, click on Custom at the bottom of the
list and type dddd mm/dd/yyyy. You might need to widen the column. You only
need to enter the date as 01/01/09 in A1, and it will automatically show you
the day as well.

Hope this helps.

Pete

"Dick C" wrote in message
...
Thanks for ur help Pete. The formula is very good if I don't have the
name
of the days in the block or if I include Sat/Sunday. I need to have
Monday
01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008
in
A3 etc. I will only be using Monday through Friday as there are only 5
trading days. So picture all the days and dates in the A column (from
1/21/08 down through 12/31/2008. Then when next Jan arrives I want to
change
the dates to line up with the days of the week in 2009. For instance if I
had started this year on Tuesday 01/01/2008, then is there an easy way for
me
to click a space to change to 01/01/2009 and have the day be Thursday -
which
the first falls on next year? This is difficult to explain I know and I'm
just not sure it can be done. But give it a go and let me know if you
think
that it can. If not, it's OK I'll just type it all in again, but it sure
takes a long, long time.

"Pete_UK" wrote:

With the date in A1, you can put this formula in A2:

=A1+1

which will add one day on to the previous day. Copy this formula down
and you will have all your dates for the whole year. You only need to
change the date in A1 to have a knock-on effect on the other dates.

Hope this helps.

Pete

On Jan 18, 5:27 pm, Dick C wrote:
I am setting up a spreadsheet of investments. The first column (A, row
1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday
01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). It takes
a long
time to set all that up. I'm now wondering if the dates can be
automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change
from
2008 to 2009? Can that be done without my having to input all the
dates
again for next year? Thanks for any help you can give?

Didn't work Pete. I have an older version of Excel. It's 97 but it did have
something close to dddd mm/dd/yyyy. Here's what I did. I typed in Monday
01/01/2008. Then, with my cursor still in that cell I clicked
Format/Cell/Number Tab/Custom and selected dd/mm/yyyy. Then I tried to copy
that to A2 and just got Monday 01/01/2008.

"Pete_UK" wrote:

Dick,

Have just re-read your post. If you need to see both the day and the date in
the cell, then you can do this with formatting - highlight column A, then
click on Format | Cells | Number tab, click on Custom at the bottom of the
list and type dddd mm/dd/yyyy. You might need to widen the column. You only
need to enter the date as 01/01/09 in A1, and it will automatically show you
the day as well.

Hope this helps.

Pete

"Dick C" wrote in message
...
Thanks for ur help Pete. The formula is very good if I don't have the
name
of the days in the block or if I include Sat/Sunday. I need to have
Monday
01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008
in
A3 etc. I will only be using Monday through Friday as there are only 5
trading days. So picture all the days and dates in the A column (from
1/21/08 down through 12/31/2008. Then when next Jan arrives I want to
change
the dates to line up with the days of the week in 2009. For instance if I
had started this year on Tuesday 01/01/2008, then is there an easy way for
me
to click a space to change to 01/01/2009 and have the day be Thursday -
which
the first falls on next year? This is difficult to explain I know and I'm
just not sure it can be done. But give it a go and let me know if you
think
that it can. If not, it's OK I'll just type it all in again, but it sure
takes a long, long time.

"Pete_UK" wrote:

With the date in A1, you can put this formula in A2:

=A1+1

which will add one day on to the previous day. Copy this formula down
and you will have all your dates for the whole year. You only need to
change the date in A1 to have a knock-on effect on the other dates.

Hope this helps.

Pete

On Jan 18, 5:27 pm, Dick C wrote:
I am setting up a spreadsheet of investments. The first column (A, row
1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday
01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). It takes
a long
time to set all that up. I'm now wondering if the dates can be
automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change
from
2008 to 2009? Can that be done without my having to input all the
dates
again for next year? Thanks for any help you can give?

In A2: 1/21/2008 (format cell as: dddd, m/d/yyyy)
In A3: =WORKDAY(A2,1)

copy from A3 down as far as needed


"Dick C" wrote:

Didn't work Pete. I have an older version of Excel. It's 97 but it did have
something close to dddd mm/dd/yyyy. Here's what I did. I typed in Monday
01/01/2008. Then, with my cursor still in that cell I clicked
Format/Cell/Number Tab/Custom and selected dd/mm/yyyy. Then I tried to copy
that to A2 and just got Monday 01/01/2008.

"Pete_UK" wrote:

Dick,

Have just re-read your post. If you need to see both the day and the date in
the cell, then you can do this with formatting - highlight column A, then
click on Format | Cells | Number tab, click on Custom at the bottom of the
list and type dddd mm/dd/yyyy. You might need to widen the column. You only
need to enter the date as 01/01/09 in A1, and it will automatically show you
the day as well.

Hope this helps.

Pete

"Dick C" wrote in message
...
Thanks for ur help Pete. The formula is very good if I don't have the
name
of the days in the block or if I include Sat/Sunday. I need to have
Monday
01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008
in
A3 etc. I will only be using Monday through Friday as there are only 5
trading days. So picture all the days and dates in the A column (from
1/21/08 down through 12/31/2008. Then when next Jan arrives I want to
change
the dates to line up with the days of the week in 2009. For instance if I
had started this year on Tuesday 01/01/2008, then is there an easy way for
me
to click a space to change to 01/01/2009 and have the day be Thursday -
which
the first falls on next year? This is difficult to explain I know and I'm
just not sure it can be done. But give it a go and let me know if you
think
that it can. If not, it's OK I'll just type it all in again, but it sure
takes a long, long time.

"Pete_UK" wrote:

With the date in A1, you can put this formula in A2:

=A1+1

which will add one day on to the previous day. Copy this formula down
and you will have all your dates for the whole year. You only need to
change the date in A1 to have a knock-on effect on the other dates.

Hope this helps.

Pete

On Jan 18, 5:27 pm, Dick C wrote:
I am setting up a spreadsheet of investments. The first column (A, row
1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday
01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). It takes
a long
time to set all that up. I'm now wondering if the dates can be
automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change
from
2008 to 2009? Can that be done without my having to input all the
dates
again for next year? Thanks for any help you can give?

You should type the date only, i.e. 01/01/08.

THEN, you apply the custom formatting of dddd mm/dd/yyyy.

It will show you Monday 01/01/2008.

If you type in "Monday 01/01/2008" (without the quotes) then Excel
will take this as a text value, and formatting will have no effect -
and the formula in A2 et seq will not work.

Hope this helps.

Pete

On Jan 19, 3:29*am, Dick C wrote:
Didn't work Pete. *I have an older version of Excel. *It's 97 but it did have
something close to dddd mm/dd/yyyy. *Here's what I did. *I typed in Monday
01/01/2008. *Then, with my cursor still in that cell I clicked
Format/Cell/Number Tab/Custom and selected dd/mm/yyyy. *Then I tried to copy
that to A2 and just got Monday 01/01/2008. *



"Pete_UK" wrote:
Dick,

Have just re-read your post. If you need to see both the day and the date in
the cell, then you can do this with formatting - highlight column A, then
click on Format | Cells | Number tab, click on Custom at the bottom of the
list and type dddd mm/dd/yyyy. You might need to widen the column. You only
need to enter the date as 01/01/09 in A1, and it will automatically show you
the day as well.

Hope this helps.

Pete

"Dick C" wrote in message
...
Thanks for ur help Pete. *The formula is very good if I don't have the
name
of the days in the block or if I include Sat/Sunday. *I need to have
Monday
01/21/2008 in A1, then Tuesday 01/22/2008 in A2 and Wednesday 01/23/2008
in
A3 etc. *I will only be using Monday through Friday as there are only 5
trading days. *So picture all the days and dates in the A column (from
1/21/08 down through 12/31/2008. *Then when next Jan arrives I want to
change
the dates to line up with the days of the week in 2009. *For instance if I
had started this year on Tuesday 01/01/2008, then is there an easy way for
me
to click a space to change to 01/01/2009 and have the day be Thursday -
which
the first falls on next year? *This is difficult to explain I know and I'm
just not sure it can be done. *But give it a go and let me know if you
think
that it can. *If not, it's OK I'll just type it all in again, but it sure
takes a long, long time.

"Pete_UK" wrote:

With the date in A1, you can put this formula in A2:

=A1+1

which will add one day on to the previous day. Copy this formula down
and you will have all your dates for the whole year. You only need to
change the date in A1 to have a knock-on effect on the other dates.

Hope this helps.

Pete

On Jan 18, 5:27 pm, Dick C wrote:
I am setting up a spreadsheet of investments. *The first column (A, row
1)
has Monday 01/21/2008, the next row (column A, row 2) is Tuesday
01/22/08 and
so forth through the end of the year (Wednesday 12/31/2008). *It takes
a long
time to set all that up. *I'm now wondering if the dates can be
automatically
changed - say from Monday 01/21 to Monday 01/19 and the years change
from
2008 to 2009? *Can that be done without my having to input all the
dates
again for next year? *Thanks for any help you can give?- Hide quoted text -

- Show quoted text -