Counting Number Of Days For Each Employee Since Last Day Of Absence / Tardy
Aug 9, 2013
I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
Captain America ................................A ..........................T .....................................T
Incredible Hulk.......................................................................................... A
Spider Man ........................................................................................................................ ........A
Iron Man ....................................................T
( I added dots because it wouldn't let me space them out?)
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Any formula to use. Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
I am trying to get the correct formula to count the number of days it has been for each employee since the last occurrence of an absence/tardy. For each employee, starting with the beginning of the year, we mark and employee as Absent as an "A" or Tardy as "T". See below the examples.
We are keeping track so that employees have the ability to make up numbers because after so many occurrences they can become terminated. If an employee has about 60 days in a row without an infraction, that employee can deduct a day from their total on the year.
Also to include a vlookup so that I could have their name as well with the number of days on a separate tab!
I am trying to calculate the number of days since an employee worked. Column A has the date and columns B,C, and D show the name of the 3 employees who worked that day. Each row shows the next day in column A with the three employees who worked that day in columns B,C, and D. I need the format of the excel sheet to remain the same. I'm looking for a formula that will calculate the number of days since each employee has worked...there are 10 different employees and only 3 work each day.
Our office has a vacation days accrual policy based on number of years worked. We have a vacation days number, based on year of employment, the employee earns monthly. I need help with a worksheet, formulas, to document each employee, the year of employment they're in, # of days they have available based on the current month (which would need to add up automatically as the year progresses), then any days they request off, and finally a remaining balance of days left.
This list goes on with every workday of the month repeat several times, with no day have any set number of entries. Also each new month is just tacked on to the previous list. All of this is in column A but there are many other columns of data with these dates. I just didn't see the need to replicate that here.
What I need is to be able to count the number of unique days per each month but I do not want to have to specify a range for the month of January, then another range for February, etc. I just want to be able to list the column A4:A1200 and have the formula select just the unique days for January. Then in another cell modify the formula for unique days in February and so on.
I have got a project that has several hundred starts and finishes. I want to count them over time to use as an indication of progress. ( I want to show this graphically)
I have got the graph working but it is pretty long handed.
I have tried using a pviot table and picvot graph which almost does it but has a few problems (if there is no data in a month then it wont create an entry for the month, and the cumulative over time is not quite working).
Both examples are in the attached file : Progress Curve ozgrid1.xlsx
i am trying to get the number of days from last time ticket paid till todate, considering the day & month of the hiring date but the year of the last time paid. say AAA hired on 15-Nov-2001 receives a ticket every 24 months; received last ticket in Nov 2011.
i want to count the number of days between 15-Nov 2011 until 31-Jul-2013 (dd & mm are from joining date yyyy from last time paid) i have a list of 1200 names with different dates of joining and different dates of payments.
I am trying to find a way of counting the total number of days medical devices were in-situ for a fairly large dataset. The worksheet has a few thousand rows. Column A - unique identifier for patient; column B - date inserted (Aug 02 - Aug 13); column C - date removed or audit date. There is no missing data, all rows have both dates. Some devices in for few days or weeks, some for up to 7 years.
I want to count each 12 month period (starting 1 Aug 02) the total "device days" for that year. (e.g. if device inserted 1 Feb 03 and removed 1 Apr 03, in year period starting 1 Aug 02 would have been in for 59 days. Another device inserted on same day and not removed until 9 Sept 05, would for first period (01/08/02- 31/07/03) 181 days, second period 366 days, third 365 days and fourth (01/08/05-31/07/06) 39 days). Therefore for the two rows, the first period total would be 240 days (59+181).
I'm basically looking for a forumla that will count each employees total scheduled work days for the month inserted and then depening upon the day it is will show how many days the employee has left to work for the month.
I developed a 14-day work schedule and I assigned each different job position a number. The different job positions are numbered 1-6 and are as follows: #1=5:30am-1:30pm, #2=6:00am-2:00pm, #3=9:30am-1:30pm, #4=12:00pm-8:00pm, #5=1:00pm-8:00pm & #6=6:00pm-8:00pm. Numbers 1,2,4,5 clock-out for a 30 minute lunch break, while the other numbers do not.
My goal is to insert the numbers 1-6 into the spreadsheet throughout the 14 days for each employee, and have Excel calculate the total number of hours for each employee in the far right column. I would also like "V" & "H" to equal 7.5 hours. This would save a lot of time instead of going through and adding up the hours with a calculator
I have a list of employees that i need to simplify into groups as listed below
Employee No's 1-19 20-50 50-199 200-499 500+
In my list of data (attached) that i receive i currently manually count the employee numbers and then put them into the relevant bands dependant on the employee sizes. Example:
Is it possible with excel, to automatically insert an employees number if their name is inserted.
Example: Column A If Smith, John is entered in Column B automatically have his employee number show 5668? Joe, Mary entered in Column A Column B to show 12345
This would save me a huge amount of time going back and fourth.
Imagine that i have an item entering my warehouse on 22/05/2008 at 21h35 and leaving on the 25/05/2008 at 5h42.
A1= 22/05/2008 and B1= 21h35 A2= 25/05/2008 and B2= 5h42
The goal is to count the full 24 hours day and the remaining hours.
For the example given i can say that on day 22 the item only says 2 hours and 25 minutes, on day 23 it stays 24 hours and on day 24 another 24 hours, on day 25 the item leaves at 5h42, so it only stays those 5h42.
So we have 2 full 24 hour days and 2h25 plus the 5h42, the the item were stored 2 days and 8 hours and 7 minutes.
The problem is when the item arrives at (example) 22/05/2008 2h00 and leaves at 25/05/2008 23h00 on another day. Lets say that on the first day the item is 22 hours stored, plus the 2 full 24 hour day and another 23 hours. So it is 2 days plus the 45 hours, that is 2 days plus 1 day and the remaining 21 hours.
how to add to this 2 full days the 1 day and 21 hours
Right, Column A on my sheet is the date that the work was completed. First thing is I need to be able to count how much work was completed per month. (I dont know why we have it all in one big sheet rather than monthly sheets but thats too logical!)
Secondly, column I is the number of days that piece of work took to complete. I need to be able to have a formula that looks at the month the work was completed, then total up the number of days that the work took.
(Example, if I have three bit of work completed in October, 1 took a total of 20 days, 1 took 15 and 1 took 10 it would = 45 days)
I know that this should be possible as I've had Excel doing more complex formulas than this.
I have a problem now with my new spreadsheet. I need to use vba to automate a process.
Date Item Holding Days
01/03/2014 A 1
[Code] ........
Scenario: On 01/03/2014, I bought A and hold it therefore holding days is 1. Then I sold it on 02/03.2014 so it became 0. Until 04/03/2014, I bought A back, therefore holding 1 day. 05th I didn't sell so I am still holding it, meaning holding days =2. Then on 06th, I sold A and buy B, resetting the holding days to 1 for a new item.
From the table above, how can I use vba to automate the last column?
I have been given a database with peoples names that require a unique number to hide/replace the name to hide their identity for a meeting and to act as a ghost system.
The problem i have is that some individuals names on the list appear more than once so i cannot simply call Joe Bloggs number 3 as he can appear further down the list at number 120.
I was wondering is there is a way to do this without manually replacing each name with a number and making and logging the persons name that corresponds to the number.
as there are 600 names and some can appear up to 4 - 5 times!
I am trying to create a rota in excel but I am struggling to get it to add up the total number of hours per week per employee.
It get a little complicated as they work split shift so they may do 4 hours in the afternoon and another shift in the evening going into the next day, example they may work 11.00 am - 3.00 pm and 8.00 pm untill 2 am.
I have converted a column of dates to days using format & dddd. However I'd now like to count how many mondays, tuesdays, etc are contained in this list, however as the data is still a date I cannot seem to do this.
I run a small holiday cottage and I want to use excel to tell me which days of the year I get most enquiries for.
Every time I get an enquiry for a certain period in the cottage, I enter the dates into excel.
I have two columns - Start of holiday and End of holiday.
What I would like to do, is give each day of the year one point if someone enquires for it.
e.g. If someone asked for 3rd january to 5 january, I would give 1 point to the 3rd and 1 point to the 4th of january (but not the 5th as that is the day they would leave!)
At the moment I find it easier to count with pen and paper than use excel for this problem.
I have an excel sheet with over 4,000 rows of records regarding our 80+ employees clocking in. There are three columns:
A: Date (MM/DD/YYYY) B: Time (HH:MM:SS) C: Employee Name
Our employees are expected to be at work (and clock-in) at or before 8am.
At first, I just filtered the data to display only rows that have a time value of between 8am and 9am (the hour when most people are tardy). However, I now realize this is not a very accurate way of reporting tardiness.
Our forklift driver, for example, almost always arrives at 7:45am, but then works around the entrance and has to scan his card (clock-in) several times throughout the morning to get back into the building. So if he's moving something outside and comes BACK inside at 8:30am, my current "filter" inaccurately reports him as tardy for that day.
Is there some way to filter this data to first reduce it down to their earliest clock-in for the day, THEN isolate only the 8am - 9am data (if we did that, the forklift driver problem would be solved because his only time for that day would be 7:45am - his TRUE clock-in). The forklift driver is just one example. Many other employees occasionally go in/out of that door and end up clocking in multiple times, so are getting wrongly reported as tardy.
I need to do a total of the dates entered in the RAW DATA sheet that fall in between today's date and 7 days ago. I am comparing it to B3 in sheet 2. Have tried a COUNTIF but cannot get it to work.
I am trying to count the number of orders written per day. I used the following formula on another sheet and it worked fine, however on this one, A8 is a date only, and F2:F2000 is a date and a time.
I tried reformating the cell for date only, and it displays only the date, however, the time information is still stored, and I can't get it to do the count. Is there a way to count the date regardless of the time?
I'm trying to count the total days between to dates but minus the fed holidays. I have a list of holidays I can ref. I like how the NETWORKDAYS function works but I need to include the weekends.
I have been struggling for almost 2 days with this problem.
I have a list of data, one column of which is the date displayed in dd/mmm/yy format. This date will always be entered by users and is variable.
I can't find a way of counting the number of occurences of each month and each day that the date represents.
So, I want to know how many occurences of March, June, May, Septemeber etc are on this list and Mondays, Tuesdays, etc.
I have tried several posssible routes, DCOUNT DCOUNTA COUNT COUNTIF SUM. I've also tried separating the date out into days & months using the MONTH & DAY functions but this didn't work either.
It also apepars (shock horror) that Excel has incorrect date & day values because entering todays date in one cell (11/05/2006) and then using the DAY function to find the day of the week for this date produces WED when it should be THU. (My system date is set as 1 Jan 1900).
I feel that Excel can't separate the months & days away from the date, since the date is stored as a numerical value and not as we humans use dates. The dates will always be manually entered by users, probably as dd-mm and Excel will automatically add in the year.
How can I count the occurences of each month and days of the week?
im trying to put together a system on worksheets that checks 'Absence' in a rolling 12 month period. The 12 month period is any 12 months and not a financial period (eg 25/12/06 -25/12/07).
I have 36 employees and want to have their names in each sheet, calander dates across the top, will mark either a 'S' for sick or 'L' for late ect against the dates if not at work.
Once an absence has been entered, on the sheet somewhere it will show how many days that person has been off (eg, 10th Oct, 16 Nov and 22 Dec would = 3 Days absence).
How do I set up the sheets to work out how many days each person has had off in a rolling 12 month period (so that it does not calculate beyond the 12 months).
I have looked on here to see if there are any programs, formulas which may work but some have lost me in my tracks.
