Counting no of days from a list of running dates for a 12 month pe

[Guest]

Hi I am trying to set up a sick leave list which will work out the cumulative
total for the last 12 months (from last day of last sick entry).

The problem I have as the ist is added to I need a function/formula to work
out 12 months from last date and then add up the no. of sick days from then
to last date.

Below is the chart

Column 1 Start date of sickness
column 2 End date
Column 3 the number of days sick in period (column 2 less column 1 in days)
column 4 the number of days sick in period above excluding week ends (column
2 less column 1 less weekends in days)
column 5 total sick days in last 12 months from date in column 2 going back
a year including weekends
column 6 total sick days in last 12 months from date in column 2 going back
a year excluding weekends

What function or formulas can I use to calculate column 3,4,5 & 6.

Im a not very experienecd in Excel so any help appreciated and make it easy
as I struggle sometimes. Thanks Bill

 

[Bob Phillips]

Hi I am trying to set up a sick leave list which will work out the
cumulative
total for the last 12 months (from last day of last sick entry).

The problem I have as the ist is added to I need a function/formula to
work
out 12 months from last date and then add up the no. of sick days from
then
to last date.

Below is the chart

Column 1 Start date of sickness
column 2 End date
Column 3 the number of days sick in period (column 2 less column 1 in
days)
column 4 the number of days sick in period above excluding week ends
(column
2 less column 1 less weekends in days)
column 5 total sick days in last 12 months from date in column 2 going
back
a year including weekends
column 6 total sick days in last 12 months from date in column 2 going
back
a year excluding weekends

What function or formulas can I use to calculate column 3,4,5 & 6.

3 - =B2-A2 and fornat as general

4 - =NETWORKDAYS(A2,B2)

5 -
=IF(B4>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),B4-MAX(A4,DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))-1),0)

6 - =IF(B4>=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),
NETWORKDAYS(MAX(A4,DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),B4),0)

 

[Guest]

Hi,

The formula below looks at the dates in column B and adds up the instances
of sickness in column E that occur within the last rolling 12 months. I think
you will find it fairly easy to understand and modify for other columns.

=SUMIF(B1:B1000,"<="&DATE(YEAR(TODAY()), MONTH(TODAY()) - 12, 1),E1:E1000)

Mike

Mike