rolling year attendance

[J Parrott]

I am trying to set up an attendance sheet in Excel that will keep a total of
the days missed but subtract the days missed after a rolling year.
I am using column A for the date, column B for the number of days missed
(typically just a 1 or .5 for each particular date) and column C for the
total days missed. I am sure it has to do with the SUMPRODUCT function, but I
am having a hard time figuring it out.

 

[T. Valko]

Do you mean you want the sum of time missed for the previous year starting
from TODAYS date?

=SUMIF(A:A,">="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),B:B)

Or, use a cell to hold todays date:

E1: =TODAY()

=SUMIF(A:A,">="&DATE(YEAR(E1)-1,MONTH(E1),DAY(E1)),B:B)

Or, if you don't need to be concerned about leap years having 366 days:

=SUMIF(A:A,">="&TODAY()-365,B:B)

=SUMIF(A:A,">="&E1-365,B:B)

Biff

 

[J Parrott]

What I am trying to do is:
Track the number of days or partial days that an employee is absent.
After one year the missed time drops off the record, so if I miss today then
next April 17th that number will not be counted in the total any more.

Our employees are allowed 12 "occurrences" per rolling year, but I do not
typically check the spreadsheets every day in order to keep track of the days
that have dropped off since they are over a year old.