Count Within Time-Frame Accounting For Sick Days
Apr 3, 2008
Im trying to count people working during times of the day. They work shifts from 8am to 9pm. I need to know at each 30 minute interval of the day how many are working. IE at 12:00 or say 13:30 I need to know how many are working. I also need to be able to account for people that are sick. IE If a person works during the day I wouldnt count them as sick at night.
Im data is set up like
Column A agent name
Column B agents start time
Column C agents end time
Column D holds two variables either sick or support.
I have found a thread that counts the people working but doesnt account for the sick or support variables. I didnt link that thread as I thought it went against the rule "STATE WHAT YOU WANT TO DO, NOT WHAT YOU THINK YOU NEED TO ACHIEVE IT "
View 9 Replies
ADVERTISEMENT
Nov 17, 2005
I am using the count function for attendance tracking of Vacation, Personal Time, & Sick Time. (Example: = COUNTIF($F6:$CQ6, "V")
I need to be able to do half days. I have tried many different formulas/ways to incorporate the half day scenario even without using the count function with no success.
View 13 Replies
View Related
Aug 11, 2009
I need to be able to calculate the actual number of days worked for an employee over a specific date range. I have a data sheet containing employee, transactions, and date of transaction (date only, does not include time). The actual number of days worked may fluctuate due to time off or holidays, so I need to be able to calculate this individually by employee. For example, with columns Employee, Transaction, & Transaction Date:
123 ABC 07/01/2009
123 ABC 07/02/2009
123 DEF 07/02/2009
123 GHI 07/02/2009
123 ABC 07/03/2009
123 ABC 07/07/2009
123 DEF 07/07/2009
123 ABC 07/08/2009
123 ABC 07/09/2009
etc....
Should return 4 work days for employee 123 over the date range 07/01/2009-07/07/2009
View 9 Replies
View Related
Mar 4, 2008
I am trying to do a timesheet spreadsheets that lists employees clockin and clockout times
Name Start End num hours worked
Cory 02:00 04:00 2
Jack 23:00 05:00 6
Fred 10:00 17:00 7
and then go through the list and and count the number of employees in a certain range.
time range number of employees working
05:00-06:00 1
06:00-07:00 2
07:00-08:00 6
08:00-09:00 5
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00..............
View 4 Replies
View Related
Jan 27, 2010
I’ve tried formulas for this but nothing is working, and was wondering if there was some vba that would do the job
Please find attached a sample sheet of where I’m up to.
I’m trying to calculate when someone goes sick to see whether they are entitled to company sick pay (CSP) Statutory sick pay (SSP) and most people will have waiting days (W D) before qualifying for CSP.
A6:A735 have set dates for two years
D6:D735 have the sick days an employee has taken
B6:B735 Matches the sick date to the date in column A
G2 has how many days entitlement for CSP
H2 has how many waiting days (W D) before CSP can start
In Column C i need CSP, SSP, or W D next to the number 1
And if someone only has 1 day sick they would not qualify for any of the above and I just need “S” in column C.
View 14 Replies
View Related
Jan 8, 2009
I am trying to put a time sheet together that will place the number 8 in a cell when the work "sick" or vacation is in another cell.
For example, if I am out sick on monday, I would like to put "sick" in the cell next to monday and have the number 8 be placed in another place. Then If I am out "sick for three days I would like all of those "sick" days to add up to 24.
View 5 Replies
View Related
Oct 6, 2007
I have a calender with days in the columns and months in the rows. However I have 3 rows for each month. I'm using text and values combinations in the cells to record data
Example First Sheet
Feb Rows, 11,12&13
Columns C,G Dates 1st to 5th
Therefore 1st of Feb has 3 cells to enter data, ie Basic Hours, Sickness, Holiday
Currently I'm defining a name range for each month, ie DFeb. Currently I'm recording each of the rows into columns in another sheet and the date in the row, by using a Hlookup on the date and a vlookup on the month
ie Second Sheet...............
View 5 Replies
View Related
Jun 25, 2008
Please see attached spreadsheet on what I am trying to achieve.
In the data tab I have my base data, which is dates across the top and names down the side. I have information in the middle such as 'H8' which means that the person was on holiday for 8 hours on that particular day, and 'S8' which means they were sick for 8 hours on that day.
I want a macro that will rearrange the information in the table and put it into the report format which is in the report sheet.
If you see the spreadsheet you will probably get a better idea of what I am trying to achieve.
View 5 Replies
View Related
Dec 19, 2008
i am trying to find the answer for a value that my change from time to time. the example is dealing with time, a 24 hour period. the cell entry my look lilke.... "SE/10". that is where se = sick time used out of the 24 hour period. so in the example "SE" = 14. but this may change depending on how many hours taken
View 8 Replies
View Related
Apr 26, 2011
I work only on Wednesdays and Thursdays. I'd like to calculate the number of my working days between two dates.
NETWORKDAYS seems like the right place to start, but I can't think of how to tell it to ignore Mondays, Tuesdays and Fridays as well as the weekend days.
View 5 Replies
View Related
Aug 1, 2013
I'm having trouble defining the time overlaps on continuous days for the varying results of the runs for my simulation.
For the following data set, without counting overlaps there are 18 total spare requirements, but if I take into account their overlaps in their duration there should be a total of 11 per day. For example in row 4 this event overlapped in the same day with the event of row 5 so both had only 2 overlaps as a result (So there is not possible to be 1 as an answer for overlap, there is only from 2 to "n" amount or 0).
But if an event overlaps with more than one in their duration the result would be their maximum overlap during this time frame.
So then I have for each event an Starting date, starting time, duration, ending date and ending duration.And for each day I would need the total amount of overlaps occurred for the duration of each event and from those I would take their maximum amount of overlaps occurred per day. To obtain their real requirement as shown on the file.
The manual procedure I'm using looks like this for the first day: (Graphical representation)
Event 1 occurs from 17:02 to 20:29 and only overlaps with Event 3, so it's 2
Event 2 occurs from 20:38 to 23:38, also only overlaps with Event 3, then its also a 2
For Event 3 in its whole duration only overlapped with Even 1 and Event 2, one at a time so its maximum overlaps where only 2.
And that's what I'm using so far to determine these amounts but since is a simulation, I would end up with several data from each run and this method would be too long. That's why I would need this template to paste data for each run (15 days) and obtain my results faster.
View 1 Replies
View Related
Mar 12, 2009
I need 15 minutes removed from a difference in time, if the time span falls over 9:00 am through 9:15 am.
I'm inputting time like 8:30:00 in A1 and 10:30:00 in A2, then calculating the difference in A3 (2:00:00). Is there a way to remove 15 minutes from that 2:00:00, since it falls over 9 to 9:15?
View 11 Replies
View Related
Jan 18, 2010
I have a formula that calculates only seconds and frames. The frame rate is 75 fps (0-74).
e.g., 49.50 is 49 seconds 50 frames. It will not parse Minutes.Seconds.Frames, e.g., 1.49.50 is 1 minute 49 seconds 50 frames.
What my existing formula does is converts the number to all frames then converts the answer back to seconds and frames.
I need this formula to include minutes in its calculation.
A copy of the spreadsheet is here: ...
View 14 Replies
View Related
Jun 25, 2013
How to create repeated time frame within a column like example stated as below:
Time 9:45AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
9:45 AM
10:00AM
10:15AM
View 1 Replies
View Related
Jan 17, 2014
I am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
View 1 Replies
View Related
Jun 23, 2014
Rather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.
View 7 Replies
View Related
May 27, 2012
There are dates in column C and I need to count how many days are coming due within 90 days of each date based on the today() function but do not exceed the 90 days.
Countif Today()+90
View 5 Replies
View Related
Mar 14, 2014
I am making process TAT(Turn Around Time) which required following information. In Excel 2007.
1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays
2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)
A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays
Note : Any weekend (off days) dates listed in holidays should not effect the query.
View 9 Replies
View Related
Apr 9, 2014
I'm using excel 2010 on a pc. I need to create a calender. So far I have been using the tutorial for the pop up calender. The result I'm looking for is to set up a specific time frame and have the calender provide the "due" date. I would like the due date to exclude weekends and holidays. Would I be able to do this with the pop up calender?
View 3 Replies
View Related
Sep 28, 2011
I need a formula to count how many days hours & mins between 2 dates but included in the formula need to ensure it only counts business days.
Column A 02/09/2011 13:00
Column AO 02/09/2011 13:02
I need in Column AP to show 0 00:02
The formula I have tried using is - =IF(AO3="","",AO3-A3) which doesnt work as instead of showing 0 00:02 it shows 1 00:02. I dont want it to count as 1 day just 2 mins.
Once that has been worked out I need to include WORKDAYS formula to only count working days. I have created the list on 'Controls' Tab Column A10:A47.
merge these together to create the one formula?
View 6 Replies
View Related
Jun 17, 2014
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
View 1 Replies
View Related
Mar 26, 2014
I got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
View 1 Replies
View Related
Sep 22, 2006
I have a spreadsheet with a list in column A, and starting from column D in row 1 are daily dates e.g. 01/10/06, 02/10/06, 03/10/06. On a daily basis an x is added manually to the spreadsheet where the list criteria matches the specific date e.g
A B C D E F
1 01/10/06 02/10/06 03/10/06
2 M123 x x
3 M456 x x
4 M789 x x
What i would like to do in column C is to measure how many x's are in the last 10 days for each row. I have used formulas in the past to do something similar but I can't find them. Sorry I can't upload a sample but firewall won't allow.
View 8 Replies
View Related
May 11, 2006
I'm setting up a shift roster and need to determine whether a given time is
between the start time and end time of a shift where the shift runs across 2
calendar days
eg start 23:00 finish 06:00
View 9 Replies
View Related
Sep 1, 2008
Is there a formula to count the amount of days, for example.....a1=8/1/08 b2=8/10/08 then c1=9 days....
View 4 Replies
View Related
May 8, 2014
I have the vacation sheet so I can track my vacation hours used and available. The last thing I need is a way to count the number of days I'm not at my site. It doesn't matter why (Training, Holiday, Travel or Vacation) I need to track total days of site.
View 4 Replies
View Related
Dec 31, 2006
I would like to have a formula in a spreadsheet of personal bills that counts down the number of days until each one is due each month.
In one column, I have a digit in each cell to represent the day of the month each month that the bill is due. Someone helped me with a formula that can count down days to a date that is a higher number than the present day of the month, but can I get a formula to count the days from, say, December (or any month) 28th to a due date of the 6th (of the next month)?
View 9 Replies
View Related
Jun 27, 2007
I need to count how many in a month per day X number is =. I originally used Countif -
=COUNTIF(B3,">25252525=36")+COUNTIF(V3,">=36")+COUNTIF(X3,">=36")+COUNTIF(Z3,">=36")
The formula above works, but i end up with several long strings of Countif. I'd like to know if there is a way wherein i can use a shorter formula.
View 9 Replies
View Related
Jan 15, 2009
when I try to calculate the worked days between two dates using the formula "Networkdays" in excel, the result just count the days from Monday to Friday.
Example:
If I want to calculate the workable days from 12/1/08 (Start_date in cell A1) to 1/14/09 (End_date in cell A2) by using the formula: =NETWORKDAYS(A1,A2)
the result will be 33 days, but due to fact that I also need to count Saturdays, the result that I need must be 39 days.
View 9 Replies
View Related
Jan 14, 2007
The union has instated a new policy here and new hires now have a 120 working day probationary period. That comes out to about 6 months. ( 168 days minus the 48 days off) . what I’m looking for is a formula that will calculate each date that has passed as one starting at a –48 … also adding in to the equation that every day sick counts against that 120 and everyday of OT counts as positive brining them closer to the 120 they need.
I can figure out the math part of adding and subtracting the days. But what I can’t figure out is how do I get it to read each date as one starting from the date in cell b3
Example cell b3 has 9/25/06 cell b4 has 3/12/07 all the days that have passed count as one.
The total starts as a –48( due to the days off) with the verabial, d8 is a negative and d9 is a positive. To be added in to the total.
View 5 Replies
View Related
