Count Function :: Vacation, Personal Time, & Sick Time
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Count Within Time-Frame Accounting For Sick Days
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 Replies!
View Related
How Much Sick Time Used
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 Replies!
View Related
Spreadsheet Show Vacation Time
I am trying to create a spreadsheet that will show vacation time; accrued, taken, leftover. There are different accrual rates for years of service, and caps on carryover. I have been trying to figure this out, however the accruals for employees over 1 year are higher than they should be. Breakdowns are as follows: 1-2 years - 1 week vacation - 40 hours eligible for carryover each year 3-4 years - 2 weeks vacation - 80 hours eligible for carryover each year 5-9 years - 3 weeks vacation - 120 hours eligible for carryover each year 10+ year - 4 weeks vacation - 160 hours eligible for carryover each year
View Replies!
View Related
Count Number Of Full Time Periods Within A Time Period
I have thousands of timestamps that have a start & end date and time in 2 separate columns. (one named start and one named end...) I also have numerous set time periods that i'm interested in.. (about a dozen or so) for example 01/01/2008 - 05/01/2008, 07:30:00 - 10:00:00 What i need is to be able to count the number of times the full time period i am interested (07:30:00 - 10:00:00) in falls in between the thousands of start and end timestamps i have. The time periods must also fall within the date range specifed. So if my timestamps were Start: 01/01/2008 06:30:00 & End: 02/01/2008 11:00:00, based on the set time period above, there would be a count of 2 and if my timestamps were Start: 01/01/2008 07:05:00 & End: 02/01/2008 09:00:00 there would be a count of zero as there is not a full uninterupted timeperiod 07:00:00 - 10:00:00 between these timestamps. and if my timestamps were: Start 01/01/2007 07:00:00 & End 02/01/2007 10:00:00 the count would be zer as this is a year early!
View Replies!
View Related
IF Function To Compare Specific Time To Time In Cell
I am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be: =((I12/100)*25)/D12 After 1pm I would like the formula to be =((I12/100)*25)/B12 I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
View Replies!
View Related
"Time" Function To Find Time&date - ?hours
I m Trying to add some features to it and run into an issue. Here are the fields we are working with! A1= 13:30:00 17/12/2008 (in other words 1:30pm on date given) - Need to know proper cell number format!! B1= 23:00:00 (this represents 23 hours) - format for cell is [h]:mm:ss C1= Unknown function (This field is my problem!!) Answer here SHOULD be "14:30:00 16/12/2008" I need a function for C1 (using A1 and B1) that will give me the time and date 23 hours earlier or however many hours:mins:secs is specified in B1! Also need to know what number format to use for both A1 and C1
View Replies!
View Related
Display End Time Automatically Upon Entering Start Time And Time Usage
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View Replies!
View Related
Stop Vacation Hours Calculation On Vacation Day
i didn't realize is that my current funcation that calculates vacation hours... will increase after a new year. i'd rather have it not increase until they are "reset" for the new year. how do i stop the function? =VLOOKUP(DATEDIF(A8,TODAY(),"y"),$S$8:$T$10,2) basically goes to a lookup table with the caculations. PROBLEM: if a user's anniversery date passes, they may go from a 1-2 year status to 3 year bump... this will auto adjust the amount of vacation hours they have. if the reset button is ran to calculate the vacation hours, it might over calculate giving the user 40+ extra carryover hours. anyway i can make this vlookup stop when the current date is or has passed the anniversery date, yet has not been reset? maybe a count down timer, not sure. http://www.ozgrid.com/News/excel-eva....htm#ExcelTips
View Replies!
View Related
Vb Or Function: Reset Vacation On Anniversary Date
SOLVED: adjusted functions. creating a vba macro for other reset goals. other goals have changed as well. I have a table where the subsheets have the specific days that an employee uses vacation, sick, personal or other hours of off time. i wrote alot here to get my goals/thoughts down my goal is to master one section then apply it to the other vacation date types. i'm stuck on going with a formula or using vb. also another concern is odd months such as someone hired 10/31 compared to a month like 6/30. not sure if this is a real concern. a. need to reset vacation hours for the year based on annual anniversary date -"vacation hours per year" -"vacation hours remaining" =E8-('Vacation 1'!B8+'Vacation 2'!B8) this data is taken from the subsheets. there is vacation 1 for jan-jun and vacation 2 for jul-dec 1year=40 hours after 1 year anniversary =80 hours 3 years+ =120 hours Vacation only rolls over for 3 months after anniversary date (this is a concern after part a is completed) second part of my project: b. vacation hours remaining can be carried over up until 3 months after their anniversary date. at this point they are wiped back to 0 until the next anniversary date (where the carry over hours recalculate) ATTACHED TEMPLATE things i am changing in design: vacation hours per year (to include a formula to calculate anniversary date, with the amount given for 0 year, 1 year, 3 years....) rather than a manual added # (which was lets say 40 hours+ carry over hours) -thats why i am separating the carry over hours... which i'm hoping to auto calculate by taking anniversary date - 3 months in a statement to reset it back to 0 after the three month period.
View Replies!
View Related
Count Time (entries Per Hour)
I have a bunch of data and I want to be able to count the number of entries that fall within each of the 24 hour increments in a 24 hour clock. (military time) For 12:00:00 all times would be between and including 12:00:00 and 12:59:59 Column B | Count ------------------ 12:00:00 344 13:00:00 44 14:00:00 5
View Replies!
View Related
On Time Function
I finally got an ontime code to work almost the way I want it. What it does not do correctly is necessarly copy paste information for the correct hour of the day. For example if the computer goes down it will start again copy/pasting for the next hour readings. Even if its three hours latter. The log should be blank for three hours since those hours wwere missed. My code is below, how can it be fixed to run "Capture" macro for the correct time of each day and leave the rest blank? Rich Public LastSoon Function Soon() 'As Date Dim tm As Variant tm = Now tm = tm + 1 / 24 'next hour tm = tm - Int(tm) 'time only (no date) tm = Int(tm * 24) 'on the hour tm = tm / 24 - 5 / 1440 '5 minutes before hour Soon = tm
View Replies!
View Related
Specify Which Criteria To Count In The Scoring As They Are Not Always Relevant Every Time
I have a scoring system I monitor in excel (I have attached an example speadsheet). I score against a number of criteria e.g. criteria 1 scores "2", criteria 2 scores "4". My problem is that the criteria don't always attract a score, sometimes they blank. I also need to specify which criteria I want to count in the scoring as they are not always relevant every time. I need formulae that will take this into account and be ready to total up only when a score is there (COUNTIF?); and out of the range of scores I want to total up only the specified criteria at choice (SUMIF?). My COUNTIF and SUMIF skills are failing with this which is very complex for my level. What would the formula be in each instance? I have attached the file to reference the cells that I have questions about. Question ?A: - is this a COUNTIF? In this cell I want to calculate the number of occurrences where there is a score detailed, but only for specified criteria, for argument's sake criteria 1,2,3,4,7 & 8 The answer would be 5 in this instance, not 6, because Criteria "3" did not receive a score. I would want to have other cells which would calculate the number of occurences where there had been a score but using different combinations of criteria Question ?B: - is this a SUMIF? In this cell I want to calculate the total sum of scores for those specified criteria where there has been a score The answer would be 13 in this instance (3+2+4+1+3 from the scores that occurred for those specified criteria) I would want to have other cells which would calculate the total sum of the scores but using different combinations of criteria
View Replies!
View Related
Breaking Time Into Intervals And Performing A Count
Office 2003 with Windows XP This is my first post so i may not have done everything correctly. I tried doing google searches to see if anyone else has done something similar but its a tricky thing to search for. I have found pages dealing with manipulating time but none seem to be what I want I have an Excel sheet with data A1=date b1=source C1-h1 all contain data (name, location, etc, etc) but I am not interested in those. A1 (the date cell) is formatted like: m/d/yyyy" "h:mm:ss AM/PM [Sample] a2 = 8/1/2006 12:20:23 AM b2 = phone a3 = 8/1/2006 12:21:13 AM b3 = email a4 = 8/1/2006 12:33:03 AM b4 = phone This is what I am trying to accomplish: I want to break each hour, whether it contains data or not and get a count of the different types of source my desired output would be: time Phone email 12:00AM to 2 3 12:30AM My problem occurs because I am unsure how to break the times down into intervals. In my head, I would create a new column and try to break the time up into an interval. So for a2 (8/1/2006 12:20:23 AM), I think I would need to break the date and time up and then determine which interval would be needed based on the time. I was going to perform the following: =text(a2, "h" + if("mm:ss">30, 30,00) Basically, pull the hour from a2 and evaluate the mins/secs. If the min/sec is bigger than 30, put a 30 in the box. Less than 30, put double zero in the box. So for 8/1/2006 12:20:23 AM, b2 would have 12:00 since it falls in the 12 to 12:30 interval. Once I have all the intervals, I would also need to preform a count on the source cell but I think I can use a vlookup to get the count for the interval.
View Replies!
View Related
Count By Criteria Of Text & Time
I am looking to create an Excel sheet that can manage the times of access of a certain location. The information is set in a way that there are several rows, and the rows for the time I am keeping track of are in the Row "I". I need to get a count of how many people accessed the location each hour. I have managed to do this using the following formula: =COUNTIF(I:I,">=12:00:00")-COUNTIF(I:I,">13:00:00") This gives me the Count for 12:00pm to 12:59pm. However, COUNTIF can only handle one variable, and I need to put a second variable in it. In Row B, there are several different states. For the most part, they are numbers, but occasionally, there are two separate occurances that I am not supposed to count with the others. I do have to count them separately, however:............
View Replies!
View Related
Count Of Employees That Worked Within Time-frame
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 Replies!
View Related
Time Series Function
I'm trying to calculate and average using a time frame in the day. I want to use a certain number "X" that correlates to a hour/minute in the current time. How do I write a statement to get me the correct "X" value? example: 8:00 .005 8:01 .006 8:02 .0075 8:03 .0077 8:04 .008 time now is 8:03 How do I get the .0077 in excel?
View Replies!
View Related
Adding Time Function
I want to add two times together. 14:00 (time) + 03:42 (duration) = 17:42 Is there vba code to be able to do this or an excel function? I have tried searching but strangely couldnt find anything?
View Replies!
View Related
Copy Row If DATE & Time In Cell Is Between Time Span
I have a problem regarding sorting data having date and also time within a single cell. Example data (I have written it as code to preserve formatting) A B C D E F 12/5/2008 02:072/5/2008 06:0128804833363 22/5/2008 18:012/5/2008 18:0599271297 Column B is start date and time whereas Column C is end date and time. My aim is to cut and paste the whole row automatically to Sheet2 if the time is within 2AM to 8AM else leave as it is.Also I don't know anything about VBA Script.
View Replies!
View Related
Automatically Change Cell (With Time) Color After Time Period Has Passed
I have a protected worksheet. Users wish to be able to track changes in the input cells. The suggested approach for this is to temporarily disable sheet protection and allow them to change the font color, then protect afterwards. What I would like to do is: i) check whether they are in an input cell ii) if so, then prompt the user with the 'Font Color' dialog box iii) apply the font color selected to the input cell I'm struggling to find the dialog box I need. I can launch the one to change the interior color, no problem (Application.Dialogs(xlDialogPatterns).Show). But that's no use to me, I just want a color palette that specifically relates to the Font Color
View Replies!
View Related
Add This Number To The Start Time, Factor In Break Minutes And Get To The Projected Completion Time
I am trying to provide a tool for department leaders to monitor productivity for order processing in their departments. The variables I have are: Number of orders(variable), number of pickers (variable), start time(variable). Then, I know each order takes 1 picker 4 minutes to pick on average, and there are 45 minutes worth of breaks during the picking process. So after entering the variables I used =(((C3*4)/60)/D3) to come up with the time needed to process the orders. What I can't get to is how to add this number to the start time, factor in break minutes and get to the projected completion time. I have Excel 2003 at work. Clearly I need to take a class!
View Replies!
View Related
Count Unique Values, And Also Average The Time Per Assignment
I am working on a Productivity Log and can't figure out a way to count unique values, and also average the time per assignment. Assignment with the action code A - should take 2 hrs to complete, all other action codes should take 20 minutes. I need to show how many of each action codes were done for the day, and what was the average time taken to complete each. Even better, how much for A action codes, and how much for all other.
View Replies!
View Related
Count Number Of Time A Text String Appears
I have a very large spreadsheet and want to count the number of times a particular text string shows up in a column. I can't use autofilter due to the 1000 limit. Here's an example, Column C contains: Dan Parker John Doe Dan Smith Jill Smith So if I search on *Dan*, the function should return a count of 2. I've used COUNTIF before to return values when the whole cell = a certain value but in my case the cell may have 200 characters and I want to count based on a fuzzy search. I would like to do this in a function and not a macro.
View Replies!
View Related
Skip Weekends Adding Time To Date & Time
I need a formula to add just the time to ' date and time', ignoring weekends. eg: Fri 24-Aug-07 10:52 is the date and time 28:48:00 is the time If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40 But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
View Replies!
View Related
Delete Date & Time Cells Based On Time
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too. I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have. Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
View Replies!
View Related
Automate Macro Start On Time & Stop At Later Time
Can I adapt this code so that users receive the alert if they try and go to the next field without filling in the required ones, or - better - to combine it with a code that does not run a macro, but instead gives the alert, if the required fields have been missed. (I don't require anything to do with printing, this was the closest code I could find) Private Sub Workbook_BeforePrint(Cancel As Boolean) If Sheet1. Range("A1").Value = "" Then MsgBox "Cannot print until required cells have been completed!" Cancel = True End If End Sub
View Replies!
View Related
Subtracting Time/date Using NOW Function
I have a working week that starts at 07:00 on a Monday morning and finishes at 14:30 on a Friday afternoon. For planning purposes I need to know at any given point how many working hours are left. The reason for this is so that I can multiply that figure by the amount of Engineers available which will give me the amount of Man Hrs left in the week. For example - if it is 11:00am on the Tuesday, how many working hours are left ....
View Replies!
View Related
Continuous Evaluation Of Time Function NOW()
1) The following formula returns TRUE or FALSE: =AND($J$15"",(MOD(SECOND(NOW())-1,6)+10) = COLUMN()) It works fine as long as the w/b is busy doing something! 2) Otherwise if the w/b is not busy, the formula appears to be calculated only ONCE at the current time NOW(), and F9 must be pressed repeatedly to re-calculate the formula despite Tools::Options::Calculation is set to Automatic. 3) Is it possible to have NOW() representing a progressive NOW()? 4) If not, can the above formula be modified such that it is evaluated continually for, say, the next 60 or 120 seconds from the current NOW()?
View Replies!
View Related
Count IF And IF Then Subtract Two Times In 24 Hr Format To Recieve Time On Task
I get a spreadsheet downloaded to excel with a variable of 15 names. I want to create a formula that if the name Cleardale appears to take the time responding in cell BD and subtract it from the time available in cell AV so that I have the time on task and then to add up all those times in a total time on task for all cleardale listings for that day. The times are listed in the 24 hour format. I keep getting errors. Can anyone help me out on this one? The range of the cells is from AV7 to AV500 for available time and BD7 to BD500 for alert time and the range of the names is cell f7 to f500.
View Replies!
View Related
Run-time Error Every Other Time The Macro Runs
I'm not sure why this is happening, but every other time I run this one specific macro, I get a "Run-time error '1004': Paste method of Worksheet class failed". I even tried running this macro, then running a different one, then running this again, but I still got the error every other time. Every time I get the error, it highlights this line of Sheets("Regenerate Request").Paste This is all of the code up to where I get the error: Sub YesRegen() ' after user has hit Yes on the RegenerateRequest macro, this posts the new request to ' the log, generates the new file and attaches it to an email Application.Run "LogUnprotect" Range(Range("A" & ActiveCell.Row), Range("K" & ActiveCell.Row)).Copy 'Selection.Copy Sheets("Regenerate Request").Activate Application.Run "RegenFormUnprotect" Range("A40:K40").Select Range("A40").Activate Sheets("Regenerate Request").Paste
View Replies!
View Related
Calculating Time Owed Based On Time Worked
I know the title is a bit vague, but I cant think how else to word it! I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target. I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
View Replies!
View Related
Calculate Processing Time With Fixed Time Paramaters
I am wanting to calculate the the processing time for an order that takes place within normal business hours and workweek. A normal day is from 8:00 to 5:00 If a task is started at 2:00 PM Monday and finished at 10:00 AM Tuesday then the result should be 5 hours as I do not want to include any time outside of normal hours. I can figure out how to subtract dates and times but not how to bridge a day(s). My data is somewhat flexible as I have not started the project yet. I can use separate cells for the times and dates or have two cells that use both incorporate the date and time (8/27/09 2:00 PM) for start and end time or any other idea.
View Replies!
View Related
To Calculate The Difference Between The Start Time And End Time
This may be a bit vague but here goes. I have to calculate the difference between the start time and end time of a job. The only catch is, how can I avoid calculating "out of hours" time. So, if a job goes from 9am to 9am the next day, I want it to avoid calculating between the hours of 23:30 and 03:30. Another example is if a job goes from 02:00 to 04:00, I want it to avoid the tim between 02:00 and 03:00. If there is a difference in days, so the job goes overnight, how do I take that into consideration also.
View Replies!
View Related
Adding A Time Offset Based On Time Of Day
I am replicating a Matlab program which calculates tide levels at different times of day. I need to replicate it in excel to speed up data analysis and I am nearly there. what happens is I need to apply a time offset to the time of high tide at port a based on the time of day, so if it is: between 00:00 and 06:00 the high tide at port b is 81.6 minutes after the peak at port a between 06:00 and 12:00 the high tide at port b is 74.56 minutes after the peak at port a between 12:00 and 18:00 the high tide at port b is 81.75 minutes after the peak at port a between 06:00 and 12:00 the high tide at port b is 79minutes after the peak at port a I tried this formula, where CO2 has the time/date of the high tide at port a: =IF(CO2<0.75,IF(CO2<0.5,IF(CO2<0.25,CL2+(81.6/(24*60)),CL2+(74.56/24*60)),CL2+(81.75/(24*60))),CL2+(79/(24*60))) The problem is the high tide on 07/01/2005 07:45 is read as 38359.32 rather than 0.32 - is there any easy way to tell excel I'm only interested in the time not the date? I have this spreadsheet setup now to do all the other bits required and i is just the timing that is a problem.
View Replies!
View Related
Converting Normal Time To Epoch Time Format
I'm working on converting some databases. One has entries with normal human readable time format, the other uses the unix epoch time format. Is there a function or vba code that I can use in excel to convert the normal time format to epoch time? I've got a thousand or so entries, so it would be nice to find a way to do this on a large scale.
View Replies!
View Related
|
TRACKER
