Subtracting Dates

[Terry Bennett]

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.

 

[Sandy Mann]

Try:

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

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

In B1: =EDATE(A1,-1)

copy B1 across to L1

 

[Guest]

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


"Terry Bennett" skrev:

 

[Terry Bennett]

Very many thanks guys - much appreciated!

 

[Terry Bennett]

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!

 

[Peo Sjoblom]

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]

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