LinkBack URL
About LinkBacks
Many Thanks newdoverman, very much appreciated.
Many Thanks newdoverman, very much appreciated.
Last edited by Cortlyn; 02-06-2015 at 06:19 PM.
Simply change all instances of 20 in all ranges for both formulas to 386 which is what is on your sheet, re-enter formulas with Ctrl+Shift+Enter key combination and pull formulas down the very end.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
I have shown a couple of ways of creating the statistics that you want. One is a Pivot Table and the other is a summary chart where you enter the start and end dates for the period that you are interested in
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Hi
Thank you AlKey
When I change the end row number the formula stops working and the values go to "0". I tried by changing the 20's to 500, copying it down, highlighting it all and pressing Ctrl-Shift-Enter, maybe it my method that is wrong?
Thank you newdoverman
I am trying to count the days off in the period and the instances off in the period. Sick.PNG
For e.g. Elena is off on 01/01/2015 to 01/01/2015 = 1 day off and 1 instance.
Elend if off on 28/01/2015 to 28/01/2015, 29/01/2015 to 29/01/2015, 30/01/2015 to 30/01/2015, these are being counted as 3 separate instances instead of 1 instance (of 3 consecutive days.
I have attached a previous example "How-to-count...." which I am treying to adapt to my report.
Many thanks
C
Why enter every single day that someone is off? This makes no sense to me whatsoever having kept attendance records for a large office for many years. Have a start date and an end date. Use the NETWORKDAYS function and that will give you the number of days. That is 1 entry regardless of the number of days. To get the number of instances of leave, just use COUNTIF for each employee and the result is the number of instances.
Hi
Thanks newdoverman.
The problem I have is that the hours worked / absences etc. are recorded on a Timesheet System and loaded into our antiquated HR system showing a day at a time as they have been entered - hence resorting to Excel to do the calculations
My sympathies to you. Even before computers we didn't have to do that....From - To ....days on a personal timesheet in HR. The line number gave the number of occurrences. A column kept running totals and that was it.
Here is your workbook with AlKey's solution implemented. I think you will see what he referred to.
Thank You Very Much!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks