| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Calendar Logic: Recurring Dates
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:
Does anyone know a better way of approaching this? Or does anyone want to try their luck at correcting my formulas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calendar Logic: Recurring Dates
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calendar Logic: Recurring Dates
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calendar Logic: Recurring Dates
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) -- Ciao! Maurizio |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| create an excel calendar w/ recurring meetings | New Users to Excel | |||
| Query logic concerning dates | Excel Programming | |||
| Between 2 Calendar dates | Excel Discussion (Misc queries) | |||
| recurring anniversary dates | Excel Discussion (Misc queries) | |||
| recurring dates | Excel Discussion (Misc queries) | |||
Linear Mode
