Changing a date back one month

J

John

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
 
P

Peo Sjoblom

Date in A1

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



--
Regards,

Peo Sjoblom

Portland, Oregon
 
P

pinmaster

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
 
B

Biff

What about holidays?

Biff

pinmaster said:
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
Click to expand...
 
B

Biff

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
 
B

Biff

This seems to work (needs the ATP for Workday)

Disregard!

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

Biff
 
R

Ron Rosenfeld

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
Click to expand...

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
 
Top