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