Can some one please help me find a way to take a date in a previous row and
get to the same date one month ago. I do not know how to account for the 30
or 31 day discrepancy. Also, if possible could it be the last business day
one month ago. Thank you very much

Date in A1

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



--
Regards,

Peo Sjoblom

Portland, Oregon




"John" wrote in message
...
Can some one please help me find a way to take a date in a previous row
and
get to the same date one month ago. I do not know how to account for the
30
or 31 day discrepancy. Also, if possible could it be the last business
day
one month ago. Thank you very much

Try something like this:

=DATE(YEAR(E7),MONTH(E7)-1,DAY(E7)-LOOKUP(WEEKDAY(E7),{1,2,3,4,5,6,7},{2,0,0,0,0,0,1} ))

if the date falls on the weekend then it should return friday's date.

HTH
JG

"Peo Sjoblom" wrote:

Date in A1

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



--
Regards,

Peo Sjoblom

Portland, Oregon




"John" wrote in message
...
Can some one please help me find a way to take a date in a previous row
and
get to the same date one month ago. I do not know how to account for the
30
or 31 day discrepancy. Also, if possible could it be the last business
day
one month ago. Thank you very much

What about holidays?

Biff

"pinmaster" wrote in message
...
Try something like this:

=DATE(YEAR(E7),MONTH(E7)-1,DAY(E7)-LOOKUP(WEEKDAY(E7),{1,2,3,4,5,6,7},{2,0,0,0,0,0,1} ))

if the date falls on the weekend then it should return friday's date.

HTH
JG

"Peo Sjoblom" wrote:

Date in A1

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



--
Regards,

Peo Sjoblom

Portland, Oregon




"John" wrote in message
...
Can some one please help me find a way to take a date in a previous row
and
get to the same date one month ago. I do not know how to account for
the
30
or 31 day discrepancy. Also, if possible could it be the last business
day
one month ago. Thank you very much

I have no clue!

Regards
JG

"Biff" wrote:

What about holidays?

Biff

"pinmaster" wrote in message
...
Try something like this:

=DATE(YEAR(E7),MONTH(E7)-1,DAY(E7)-LOOKUP(WEEKDAY(E7),{1,2,3,4,5,6,7},{2,0,0,0,0,0,1} ))

if the date falls on the weekend then it should return friday's date.

HTH
JG

"Peo Sjoblom" wrote:

Date in A1

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



--
Regards,

Peo Sjoblom

Portland, Oregon




"John" wrote in message
...
Can some one please help me find a way to take a date in a previous row
and
get to the same date one month ago. I do not know how to account for
the
30
or 31 day discrepancy. Also, if possible could it be the last business
day
one month ago. Thank you very much

This seems to work (needs the ATP for Workday)

=WORKDAY(DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)),IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)),2)6,-2,IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)),2)=6,-1,0)),J1:J10)

J1:J10 = holidays

Biff

"pinmaster" wrote in message
...
I have no clue!

Regards
JG

"Biff" wrote:

What about holidays?

Biff

"pinmaster" wrote in message
...
Try something like this:

=DATE(YEAR(E7),MONTH(E7)-1,DAY(E7)-LOOKUP(WEEKDAY(E7),{1,2,3,4,5,6,7},{2,0,0,0,0,0,1} ))

if the date falls on the weekend then it should return friday's date.

HTH
JG

"Peo Sjoblom" wrote:

Date in A1

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



--
Regards,

Peo Sjoblom

Portland, Oregon




"John" wrote in message
...
Can some one please help me find a way to take a date in a previous
row
and
get to the same date one month ago. I do not know how to account
for
the
30
or 31 day discrepancy. Also, if possible could it be the last
business
day
one month ago. Thank you very much

This seems to work (needs the ATP for Workday)

Disregard!

Upon further testing...........crash and burn!

Biff

"Biff" wrote in message
...
This seems to work (needs the ATP for Workday)

=WORKDAY(DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)),IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)),2)6,-2,IF(WEEKDAY(DATE(YEAR(A2),MONTH(A2)-1,DAY(A2)),2)=6,-1,0)),J1:J10)

J1:J10 = holidays

Biff

"pinmaster" wrote in message
...
I have no clue!

Regards
JG

"Biff" wrote:

What about holidays?

Biff

"pinmaster" wrote in message
...
Try something like this:

=DATE(YEAR(E7),MONTH(E7)-1,DAY(E7)-LOOKUP(WEEKDAY(E7),{1,2,3,4,5,6,7},{2,0,0,0,0,0,1} ))

if the date falls on the weekend then it should return friday's date.

HTH
JG

"Peo Sjoblom" wrote:

Date in A1

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



--
Regards,

Peo Sjoblom

Portland, Oregon




"John" wrote in message
...
Can some one please help me find a way to take a date in a previous
row
and
get to the same date one month ago. I do not know how to account
for
the
30
or 31 day discrepancy. Also, if possible could it be the last
business
day
one month ago. Thank you very much

On Mon, 6 Feb 2006 21:01:02 -0800, "John"
wrote:

Can some one please help me find a way to take a date in a previous row and
get to the same date one month ago. I do not know how to account for the 30
or 31 day discrepancy. Also, if possible could it be the last business day
one month ago. Thank you very much

Target date in A1:

One Month Back:

=edate(A1,-1)

One Month Back, adjusted to either the closest or subsequent business day:

Preceding business day:

=workday(edate(A1,-1)+1,-1)

Subsequent business day:

=workday(edate(A1,-1)-1,1)

Last business day of the preceding month:

=workday(A1-DAY(A1)+1,-1)

-------------------------
If the WORKDAY and EDATE functions are not available, and returns the #NAME?
error, install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.
--------------------------

The WORKDAY function has an optional HOLIDAYS argument to exclude Holidays.
See HELP for details as to how to use it. Briefly, you enter a list of dates
in a range, and add that reference to the formula.


--ron