90 day rolling formula to count occurances.

**alaz88** · 10-31-2012, 12:55 AM

I need a formula that will count occurances within a 90 day rolling period. This is to set up violations in attendance for my employees. They are allowed to have no more than 8 points within 90 days. 1 point for every absence and .5 for every tardy. The 90 days is from the date of the occurance. For example if they were absent on Oct 1st and 5th then from the 1st until the 5th they would have 1 point. On the 5th a second point would be added for the second absence for a total of 2. On the 91st day from Oct 1st the first point would fall off but the second point would remain. Then on the 91st day from the 5th the second point would fall off. So the 90 days rolls from the date of the occurance. Also I have it set up where each sheet is a month and I have two sheets for each month, 1 for my permanent employees and 1 for my temps. I really appreciate any help I can get with this. Thank you!

**FDibbins** · 10-31-2012, 01:04 AM

sounds like you need to use the sumifS() function. but without seeing a sample of you're data, i cant say what the syntax would be. try using the sumifS() function and use the formula editor to help you. you will need to include a piece that says >=today()-90

**alaz88** · 10-31-2012, 01:13 AM

To FDibbins
attendance.jpg this is what the month of oct looks like if that helps.

**alaz88** · 10-31-2012, 01:17 AM

attendance.jpg I meant to reply to you and accidently just posted again. Here's a pic of the month of Oct. Hope this helps. Thank you!

**FDibbins** · 10-31-2012, 01:18 AM

please upload a sample WORKBOOK, not a pic, i might just send you a pic back with the solution

**FDibbins** · 10-31-2012, 01:19 AM

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**alaz88** · 10-31-2012, 01:33 AM

hope this works.

**FDibbins** · 10-31-2012, 02:25 AM

thanks for the file. looks like that is for just 1 month? would the other months be on separate sheets? in order to know where to start, the formula will (i assume) have to go back 90 days from today to determine where to start counting from...correct?

**alaz88** · 10-31-2012, 02:58 AM

We actually just started this on Oct 1 so I don't have any previous information. I figured I could just copy the sheet a few times to play around with it until I figured it out. Yes you are correct in that next month will be on another sheet and Dec will also. I kind of figured if I had the right formula I could just carry it from sheet to sheet. This may seem silly but I really am not well versed in excel so if possible you may have to dumb down your responses. For example >=today()-90, if I were to use this formula is there supposed to be anything in the ()? I was looking at other threads and became completely lost. Again I really appreciate your help.

**FDibbins** · 11-01-2012, 01:02 PM

sorry, thought I responded to this, been having problems with my ISP.

no, there is nothing required in today() - it returns today's date, give it a try and see

**DCatboone** · 11-28-2023, 02:35 PM

I had to do this for my work place. I think you'll want to stop doing what you're doing, and make it simpler. Having a separate sheet per month makes things more complicated. This is how i solved it