hi ,

Here is a proposal to start the calculation
note / without including the calculation for Monday or Friday


example if date is May 28, 2012 (iso international : 2012-05-28)

start of year 2012-01-01 =DATE(YEAR($A$1),1,1)
end of year 2012-12-31 =DATE(YEAR($A$1),12,31)
start of month 2012-05-01 =DATE(YEAR($A$1),MONTH($A$1),1)
end of month 2012-05-31 =DATE(YEAR($A$1),MONTH($A$1)+1,1)-1
start of quarter 2012-04-01
=DATE(YEAR($A$1),INDEX({1;4;7;10},MATCH(MONTH($A$1 ),{1;4;7;10},1)),1)
end of quarter 2012-06-30
=DATE(YEAR($A$1),INDEX({10;7;4;1},MATCH(MONTH($A$1 ),{1;4;7;10},1)),1-1)

--
isabelle


Le 2012-11-30 00:00, isabelle a écrit :
hi,

you should define what it is in your area
it is not the same all around the world
what is the first day of the year ? 1 or Monday

start of month ? 1 or Monday
end of month ? (27-28-30-31) or Friday or Saturday
etc....

--
isabelle


Le 2012-11-29 00:46, Talka a écrit :
Let's say I have a number of recurring tasks. These tasks occur either
monthly, quarterly or yearly. Each task is due either several days
_after_the_start_ of each month/quarter/year, or several days
_before_the_end_ of each month/quarter/year. This means there are six
types of recurring tasks:

- Due X workdays after start of month
- Due X workdays before end of month
- Due X workdays after start of quarter
- Due X workdays before end of quarter
- Due X workdays after start of year
- Due X workdays before end of year
For each of these six types, I want to feed Excel the X variable above
(e.g., "Due *-_3_-* workdays after start of month"), as well as a date.
I then want Excel to tell me the next time the recurring task is due
after the date provided. For example, if I feed Excel X=3 and
date=2012.11.28, Excel should tell me:

- Next time "Due 3 workdays after start of month" occurs:
2012.12.05
- Next time "Due 3 workdays before end of month" occurs: 2012.11.28
- Next time "Due 3 workdays after start of quarter" occurs:
2013.01.03
- Next time "Due 3 workdays before end of quarter" occurs: 2012.12.26
- Next time "Due 3 workdays after start of year" occurs: 2013.01.03
- Next time "Due 3 workdays before end of year" occurs: 2012.12.26
I've spent a few hours messing around with the WORKDAY(), EOMONTH(),
DATE(), FLOOR() and CEILING() functions. Nothing I try works.
Particularly difficult are dates near the calendar cutoffs (e.g.,
2012.12.31). Find below my flawed attempts. In these formulas, cell A1
is the number of workdays (i.e., "X") and cell A2 is the date.

- Next time "Due X workdays after start of month" occurs:
=WORKDAY(EOMONTH(A2,-1),A1)
- Next time "Due X workdays before end of month" occurs:
=WORKDAY(EOMONTH(A2,0)+1,-A1)
- Next time "Due X workdays after start of quarter" occurs:
=WORKDAY(DATE(YEAR(A2),FLOOR(MONTH(A2)-1,3)+1,1)-1,A1)
- Next time "Due X workdays before end of quarter" occurs:
=WORKDAY(DATE(YEAR(A2),CEILING(MONTH(A2),3)+1,0)+1 ,-A1)
- Next time "Due X workdays after start of year" occurs:
=WORKDAY(DATE(YEAR(A2),12,31),A1)
- Next time "Due X workdays before end of year" occurs:
=WORKDAY(DATE(YEAR(A2),12,31)+1,-A1)
None of these are correct in their current formats.

Does anyone know a better way of approaching this? Or does anyone want
to try their luck at correcting my formulas?