I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are dates -
the first of each month going back for 12 months. So, A1 is 1 Nov 2007, B1
is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1 Dec
2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load one
of the add-ins (Analysis Toolpak?) and I notice that when the spreadsheet is
e-mailed to users who have not loaded this, the NAME error is shown until it
is loaded. As the recipients are a large group with mixed IT abilities, I
can forsee problems asking them to do this!

There must be another way?!

Thanks.

Try:

=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are dates -
the first of each month going back for 12 months. So, A1 is 1 Nov 2007,
B1 is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1 Dec
2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load
one of the add-ins (Analysis Toolpak?) and I notice that when the
spreadsheet is e-mailed to users who have not loaded this, the NAME error
is shown until it is loaded. As the recipients are a large group with
mixed IT abilities, I can forsee problems asking them to do this!

There must be another way?!

Thanks.

In B1: =EDATE(A1,-1)

copy B1 across to L1


"Terry Bennett" wrote:

I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are dates -
the first of each month going back for 12 months. So, A1 is 1 Nov 2007, B1
is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1 Dec
2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load one
of the add-ins (Analysis Toolpak?) and I notice that when the spreadsheet is
e-mailed to users who have not loaded this, the NAME error is shown until it
is loaded. As the recipients are a large group with mixed IT abilities, I
can forsee problems asking them to do this!

There must be another way?!

Thanks.

in B1 type
=DATE(YEAR(A1),MONTH(A1)-1,DAY(A1))


"Terry Bennett" skrev:

I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are dates -
the first of each month going back for 12 months. So, A1 is 1 Nov 2007, B1
is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1 Dec
2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load one
of the add-ins (Analysis Toolpak?) and I notice that when the spreadsheet is
e-mailed to users who have not loaded this, the NAME error is shown until it
is loaded. As the recipients are a large group with mixed IT abilities, I
can forsee problems asking them to do this!

There must be another way?!

Thanks.

Very many thanks guys - much appreciated!

"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are dates -
the first of each month going back for 12 months. So, A1 is 1 Nov 2007,
B1 is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1 Dec
2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load
one of the add-ins (Analysis Toolpak?) and I notice that when the
spreadsheet is e-mailed to users who have not loaded this, the NAME error
is shown until it is loaded. As the recipients are a large group with
mixed IT abilities, I can forsee problems asking them to do this!

There must be another way?!

Thanks.

Just looking again at this, the EDATE solution seems to work fine but the
others come unstuck when going back beyond January as the next month becomes
1/12/2007 rather than 2006!


"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are dates -
the first of each month going back for 12 months. So, A1 is 1 Nov 2007,
B1 is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1 Dec
2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load
one of the add-ins (Analysis Toolpak?) and I notice that when the
spreadsheet is e-mailed to users who have not loaded this, the NAME error
is shown until it is loaded. As the recipients are a large group with
mixed IT abilities, I can forsee problems asking them to do this!

There must be another way?!

Thanks.

EDATE is part of the ATP add-in just like EOMONTH so if that was the reason
you posted in the first place I don't see how it would solve the problem

Anyway, the other 2 solutions return the same values as EDATE and why would
you want to get
12/01/2006 if the date in A1 is 01/01/2008? Nothing in your OP indicates
that


--


Regards,


Peo Sjoblom



"Terry Bennett" wrote in message
...
Just looking again at this, the EDATE solution seems to work fine but the
others come unstuck when going back beyond January as the next month
becomes 1/12/2007 rather than 2006!


"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are dates -
the first of each month going back for 12 months. So, A1 is 1 Nov 2007,
B1 is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1 Dec
2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load
one of the add-ins (Analysis Toolpak?) and I notice that when the
spreadsheet is e-mailed to users who have not loaded this, the NAME error
is shown until it is loaded. As the recipients are a large group with
mixed IT abilities, I can forsee problems asking them to do this!

There must be another way?!

Thanks.

Sorry, my error - does revert to 1/12/06 as required.


"Peo Sjoblom" wrote in message
...
EDATE is part of the ATP add-in just like EOMONTH so if that was the
reason you posted in the first place I don't see how it would solve the
problem

Anyway, the other 2 solutions return the same values as EDATE and why
would you want to get
12/01/2006 if the date in A1 is 01/01/2008? Nothing in your OP indicates
that


--


Regards,


Peo Sjoblom



"Terry Bennett" wrote in message
...
Just looking again at this, the EDATE solution seems to work fine but the
others come unstuck when going back beyond January as the next month
becomes 1/12/2007 rather than 2006!


"Terry Bennett" wrote in message
...
I'm sure there must be a simple way of doing this ...

I have a worksheet where the column headers (ie; cells A1:L1) are
dates - the first of each month going back for 12 months. So, A1 is 1
Nov 2007, B1 is 1 Oct 2007, etc.

What I need is for cells B1:L1 to update automatically when I change the
value of A1 to the first of the current month. So, when A1 becomes 1
Dec 2007, I need B1 to become 1 Nov 2007, etc.

The only way I can think of doing this is by using EOMONTH. Hence, B1 =
EOMONTH(A1,-2)+1. This works OK but to use this function I had to load
one of the add-ins (Analysis Toolpak?) and I notice that when the
spreadsheet is e-mailed to users who have not loaded this, the NAME
error is shown until it is loaded. As the recipients are a large group
with mixed IT abilities, I can forsee problems asking them to do this!

There must be another way?!

Thanks.