If you charge a set minimum time for care (in this article we will use 30 minutes as our example) you can use the Attendance Report and an excel formula to find those children who stayed under 30 minutes and round them up to be charged for 30 minutes.

This article will show you how you can copy the spreadsheet to another sheet and use this formula:


to replace the data of the original report with the minimum time. 

Basically, this formula says that the cells of the original sheet should contain whatever numbers are on the copy of the spreadsheet unless that number is less than 0.5, in which case it should be "0.5". Then you can copy the formula across the entire report, redo the sum in the Attended Time column with the new totals and you're done. 

While this process may seem difficult at first, hang in there, it is simple once you get the hang of it!  

Ready to go? We'll walk you through it:

Step1: Open the Detailed Report under Attendance-->Monthly

Step 2: Select All and then Copy the entire report. Then at the bottom, click the + button next to the tab that says "Monthly Sign In Out Report". This opens a new Sheet in excel. Click on cell A1 in the new sheet and then Paste the report here. Then, on the bottom, click on the tab for "Monthly Sign In Out Report" to navigate back to the original report (all further work will be completed in the original report). 

Step 3: Click on cell M3 (this should be the first day of the month for the child at the top of the report) and enter the formula from above and press enter. =IF(AND(Sheet1!M3<0.5,Sheet1!M3>0),(0.5),(Sheet1!M3))

Step 4: Now that the formula is in that cell, select the cell by clicking on it again. You'll notice that the cell is outlined in green and there is a small green plus in the lower right hand corner. Double click on that little plus to copy that formula down the entire column to the last child on the report. The whole column should be outlined in green and the formula will be applied to all of them. 

Step 5: Now you can grab the square again and pull it all the way across to the right to copy the formula for all the days of the month. When you let go this time, all the cells should update correctly. 

Step 6: To run totals for the newly adjusted time: put the following formula on L3 of the original spreadsheet:
then drag the box across the entire row (from M3 to the end of the month) and type an end parenthesis ) and hit "enter" to total the student's adjusted hours for the month. See below:

Step 7: Copy this formula down the "Attn Time (hrs)" column for all the children and you have all your new adjusted totals

***Important disclaimer: Keep in mind that when you round up to calculate an adjusted attended time, the total attended time for a given child/given month may be different than the total that the parents will see in the Parent Portal or other reports.
As always, we recommend close communication with parents regarding any changes made to the attendance or billing reports. You may want to add an explanation in the invoice/statement "note" so the parent understands that the difference in Attended time is due to the minimum charge. 

Did this answer your question?