Calendar Logic: Recurring Dates

[Talka]

Let's say I have a number of recurring tasks. These tasks occur eithe
monthly, quarterly or yearly. Each task is due either several day
_after_the_start_ of each month/quarter/year, or several day
_before_the_end_ of each month/quarter/year. This means there are si
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 abov
(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 du
after the date provided. For example, if I feed Excel X=3 an
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 A
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 wan
to try their luck at correcting my formulas

 

[isabelle]

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]

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)

 

[Maurizio Borrelli]

Il giorno venerdì 30 novembre 2012 08:55:08 UTC+1, isabelle ha scritto:

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)

Hi,
Alternative formulas:
end of month 2012-05-31 =DATE(YEAR($A$1),MONTH($A$1)+1,0)
start of quarter 2012-04-01
=DATE(YEAR($A$1);INT((MONTH($A$1)-1)/3)*3+1;1)
end of quarter 2012-06-30
=DATE(YEAR($A$1);INT((MONTH($A$1)-1)/3)*3+3+1;0)