If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating absence periods in a rolling 12 months in excel
|
#3
|
|||
|
|||
Calculating absence periods in a rolling 12 months in excel
Thanks Domenic - think I'm getting the hang of this now.
Domenic wrote in message ... Hi Mark, Try, 12 Months: D1=DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR( NOW())-1,MONTH(NOW()),DAY(NOW())-(DAY(NOW())))))) 3 Months: D2=DATE(YEAR(NOW()),MONTH(NOW())-3,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR( NOW()),MONTH(NOW())-3,DAY(NOW())-(DAY(NOW())))))) 6 Months: D3==DATE(YEAR(NOW()),MONTH(NOW())-6,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR (NOW()),MONTH(NOW())-6,DAY(NOW())-(DAY(NOW())))))) 9 Months: =DATE(YEAR(NOW()),MONTH(NOW())-9,DAY(NOW())-(DAY(NOW())DAY(DATE(YEAR(NO W()),MONTH(NOW())-9,DAY(NOW())-(DAY(NOW())))))) Then, put this formula in E1 and copy down: =SUMPRODUCT(--($A$1:$A$1000=D1),--($A$1:$A$1000=$C$1),--($B$1:$B$1000= D1),--($B$1:$B$1000=$C$1)) Hope this helps! In article , (Mark) wrote: (Mark) wrote in message . com... Thanks Frank However have put calculations in and am getting '0' as a result. The dates in column A are as follows (first date of absence period): 11/07/2003 15/02/2003 19/10/2003 10/05/2004 02/06/2004 The dates in column B are as follows (last date of absence period): 15/07/2003 24/02/2003 28/10/2003 14/05/2004 04/06/2004 Column C1 reads: 14/06/2004 (=now()) Column D1 reads: 01/06/2003 (using your calculation to get rolling tweleve months) Column E1 reads: 0 absence periods Looking at columns A to B, those periods of absence that fall within 01/06/2003 and 14/06/2004 should be 4 Any thoughts? Would also like to apply calculations to same data set to show in cells: Number of periods of absence in last three months Number of periods of absence in last six months Number of periods of absence in last nine months Thanks again. Mark |
#4
|
|||
|
|||
Calculating absence periods in a rolling 12 months in excel
|
Thread Tools | |
Display Modes | |
|
|