Calculating Total Time For Individual With Multiple Start / End Times
Feb 12, 2014
I am trying to come up with a formula that calculates total time someone has worked in a day. The scenario is an individual will work at a home and start working with an individual. Their start/end times look like this in a pivot:
Min Start Max Start Min End Max End
Location A+Counselor A 8:56 AM4:01 PM 1:11 PM 7:00 PM
Location A+Counselor B 12:00 AM 8:00 PM 6:00 AM 11:59 PM
Location B:Counselor C 7:00 AM 12:00 PM 2:00 PM 4:00 PM
Location C+Counselor D 8:00 AM 8:00 AM 4:00 PM 4:00 PM
Some people work split shifts while others work a straight shift. The formula I created was this:
=IF(OR(B9=C9,E9=D9,D9=C9),E9-B9,IF(D9>C9,((E9-D9)+(C9-B9)),IF(C9>D9,((D9-B9)+E9-C9),"New Formula Needed")))*24
(I use a pivot table to show max min for start and end times)
This works great except for the individuals that have multiple punches during the same time frame. The one scenario I am having trouble solving for is when someone punches in more than once during their shift displaying. This occurs when a counselor starts a shift working with one person but then adds another person mid shift. An example of this could be:
Location A+Counselor E Min Start Max Start Min End Max End
Consumer 1 1:00 PM 1:00 PM 8:30 PM 8:30 PM Total Time: 7.5
Consumer 2 12:00 PM 12:00 PM 2:35 PM 2:35 Pm Total Time: 2.6
Pivot says that they worked a total of 10.1 because it is grabbing the max and mins and calculating. The actual total time worked is 8.5 hours in reality.
The raw data comes in like so:
Location Counselor Consumer Start Time End Time
A A A 1:00 PM 8:30 PM
A A B 12:00 PM 2:35 PM
A A C 12:00 PM 5:00 PM
Is this solvable with a formula?
View 1 Replies
ADVERTISEMENT
Sep 20, 2013
I'm currently working on a rota for which I would like the worksheet to automatically tell me how many staff members I have beginning their shift before 9am and those finishing after 7pm. I currently have this working via a very crude set of IF statements for each staff member for each day of the week, returning 1 if true and 0 if false. Then I have a sum statement at the bottom of each day. Is there a much tidier and simpler way for me to calculate this?
I've attached the worksheet. A quick note is in Q52.
View 3 Replies
View Related
Feb 22, 2010
I have a number of processes that I would like to calculate the finish times of.
For example, Process A may take 18 hours to complete, Process B may take 28 hours to complete and Process C may take 125 hours to complete etc
However these processes only run during certain times, i.e. 9am to 5pm.
Ideally I would like to setup a spreadsheet that when given the start time and process time calculates the (date and) time the process will finish.
I can get it to work on a 24 hour day but I've been tearing my hair out trying to take out the none-working part of the day (the 5pm to 9am).
View 9 Replies
View Related
Oct 1, 2009
I’m working on a timesheet and I need to separate the hours worked that are before 6 am from all the others. For example if someone works 1:00 am to 8:00 am I need a cell to populated with 5 representing the hours worked before 6 am. The formula below works fine except when the start time is 12:00 am. I am also having trouble if the start time is before midnight like in a 11pm to 5 am shift.
=(IF(AND(S3<=$AG$97,S3>=$AG$73),(($AG$97-S3))*24,0))
S3 is the start time
AG97 is 6:00 am
AG73 is 12:00 am
View 4 Replies
View Related
Apr 30, 2009
I am trying to create a time sheet where there will be a start time - end time - number of hours (difference between the 2) - pay rate - total pay, but am getting strange results!
View 9 Replies
View Related
Dec 26, 2013
We have a person who work in 24/7 support. One person work passive 24 hour, but it 24 hours are divided in to next shifts: normal 06-18, over hours 18-22, night hours 22-06.So if we have some one who is supporting client say from 17h till 23h, we have next results:
Name Start time End Time 06:00 - 18:00 18:00 - 22:00 22:00 - 06:00
John 17:00 23:00 1:00 4:00 1:00
Is it possible to in time range enter formula who subtracted end time from start time, but taking into account 24 hours?
View 9 Replies
View Related
Mar 12, 2009
i want to calculate the TAT between two times. the TAT target is <= 2 hours. i used the following formula
a1 has 3/13/2009 (received date)
b1 has 7:08 AM (received time)
c1 has 3/13/2009 (completed date)
d1 has 9:08 AM (completed time)
e1 has TAT formula :- '=IF((D1-B1)*1440<=120,"Met TAT","Not Met TAT")
however this formula does not work in the following conditions.
In these conditions, it is considered that TAT is met.
1. When the difference in time is <=2 hours .... for TAT calculation, on working days and working hours are taken into consideration.
To illustrate.
Day begins : 8:00 AM
Day ends : 4:00 PM
If job is received at 3:30 PM and completed the next working day by 9:30 AM, then it is considered TAT is met.
calculation = 4:00 PM - 3:30 PM = half hour + next day's 9:30 AM - 8:00 AM = 1.5 hours, therefore, total working hours used to complete the job is within the agreed TAT.
If job is received and completed on non working days and during non working hours, it is considered TAT met.
If job is received almost at the end of the day, say, 3.30 PM and job is completed at 8:00 PM same day, then it is considered TAT met, rationale, only half an hour of working hours used to complete the job.
View 11 Replies
View Related
Nov 14, 2008
Need a ormula that will calculate various lengths of time within a column.
For example: I would like to be able to add
02:43 (2 minutes 43 seconds) plus
01:10 (1 minute 10 seconds) plus
05:15 (5 minutes and 15 seconds)
..and accurately arrive at the sum of
09:08 (9 minutes 8 seconds)
Currently, whenever I input the value of 02:43- and assign the value of 'TIME'.. it reads it as 14:23 (clock time..not length of time.) I will only be using data that follows the format of mm:ss (minutes:seconds).
View 2 Replies
View Related
Aug 15, 2007
I need to create a formula to calculate monthly hours worked (144.20) by hourly rate (£14.25). So far everything ive tried has given me awrong answer. I cant seem to make the total right. From what ive come up with (Not good) the total accepts the hours-just not the minutes.
View 8 Replies
View Related
Apr 18, 2013
Calculating Lead time (in hours) between two dates/times, excluding holidays and weekend
Start Time
End Time
Lead Time
12/26/2012 15:50
1/2/2013 12:38:00
??????????
View 4 Replies
View Related
Apr 14, 2009
I have set up a spreadsheet to calculate time as followed:
A1 - Start Time
B1 - Finish Time
A2 - Hours Worked:
=IF(B1<A1,B1+1,B1)-A1
C1 - Start Time
B1 - Finish Time
C2 - Hours Worked:
=IF(D1<C1,D1+1,D1)-C1
and so on.........
View 10 Replies
View Related
Apr 10, 2014
I would like to add up multiple time in and time out for my pay sheet.
As you can see, the total hours worked for Weed A is correct, but the Total Hours Worked for Monday is incorrect. The forumla I am currently using is:
=((B12-A12)*24)+((B13-A13)*24)+((B14-A14)*24)
But I would just like the total hours worked for each day to be the actual hours (2.5 hours).
View 2 Replies
View Related
Feb 26, 2009
I'm creating a spreadsheet that has:
- budgeted dollar amount
- entry each week for that week's total expense
- total expense to date
Question: how can I hold the value for "total expense to date" so that when the new week's info is added the previous total is not lost?
View 13 Replies
View Related
Apr 16, 2007
In column A I have a list of 5 Auditors labelled Q1 - Q5, 5 Coolum’s across in column F I enter in their scores as a % e.g. 80%. ...So Q1 - 50%, Q2 - 60%. In column A37-A41 I have Q1-Q5 listed, in Column B37-B41 I need to calculate the average deviation per Auditor eg. If Q1 has 2 entries of 50% and 75% return average value in cell A37 which should be 62.50%. I am trying to calculate the average for each Auditor. find attached example.
View 2 Replies
View Related
Jan 22, 2014
My Problem is I have a workbook with multiple sheets with a possibility of a number between 2 and 999 occurring. I am looking for a formula that can display a table on the "total" worksheet for every ID number that has been entered and the number of times the ID number is displayed.
View 7 Replies
View Related
Mar 7, 2009
I need a formula in excel to convert start times and end times and minus out lunch time taken and then give total hours worked. For instance,
Column D Column E Column F Column G
Start End Lunch Total Hours Worked
10:01AM 7:08PM 1:01 (formula to convert hours worked)
View 9 Replies
View Related
Apr 21, 2014
I have an excel workbook which requires us to enter the log details of some sites and then a report is generated using these logs (report attached) . [URL] .......
I want that excel should automatically identify the site and calculate its outage with the following formula:
Outage %age= Total Outage(min)/100.80
and add it in the respective column while identifying the outage.
e.g. if outage is of power then the calculated %age outage should be pasted in the power outage column in front of the respected site.
e.g. in the sheet attached above the first row in the first sheet states that SKU2326 was down for 50 mins
Now %age outage will be
Outage %age= 50/100.80
Outage %age=0.496
Now as the outage is related to power so this outage should go into power column in sheet number 3 labelled as "Network Total Up time Week#17"
Now the next time SKU2326 becomes down it should be added with this 50 mins and the outage calculated as presented above.
View 1 Replies
View Related
Jun 28, 2013
how to calculate time overlaps between two ranges using a MIN/MAX formula. Now I need to do the same, but for three (or more) ranges. I have searched and searched, but I can't find a solution. I've attached a sample spreadsheet.
In the example, John is working three jobs, job one from 8AM-4PM, job two from 12PM-2PM and job three from 9AM-11AM. There is a total of 4 hours of overlap, 2 hours from job two and 2 hours from job three. How can I get Excel to calculate that for me?
View 3 Replies
View Related
May 4, 2007
1: Clock shoud tick monday to friday 8:00am to 4:pm only
2: there are 4 coloums where date recieved, date sent, time recieved, time sent are available.
3: the TAT should show the number of hours taken to from the time a case has been recieved to the time it has been sent
View 9 Replies
View Related
Dec 19, 2008
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 2 Replies
View Related
Apr 23, 2008
I have multiple tabs for each month (Jan, Feb, etc) for the 2008 year and I have a Summary tab.
For the individual months, I have each calendar day in a column and whether the day was for sick, vacation, etc in the rows. So if someone was sick in April, I would mark the day they were out of the office. And so on...
Well now I need to figure out how I can take those individual dates and total them up in the summary page automatically.
View 9 Replies
View Related
Jul 3, 2006
I have the same data (but not necessarily on the same cell for everyday) for everyday of the month. Now I need to calculate what is the total for an individual for the whole month. Example,I need to total John's data for the month of January from the individual sheet for Region 1, Region 2 and region 3. I tried the sumif function as below but it does not seem to work. =SUMIF('Jan 1:Jan 3'!B5:B7,"John",'Jan 1:Jan 3'!C5:C7). I have simplified the file as an attachment below.Appreciate need as the original file is for 6 months and I need a way to compute the data.
View 4 Replies
View Related
Jul 8, 2014
I have a data which has a start time, end time, Stay Time(End-Start) and # of subjects. I am trying to create a graph, line, bar or histogram which shows how many subjects were there at a specific time. So the horizontal axis would have time from 00:00 to 24:00 and vertical axis would have the total # of subjects or the %.
Start End Subject Stay
01:00 02:00 1 01:00
01:00 01:45 1 00:45
02:00 21:00 1 19:00
03:10 14:10 1 11:00
The data set is huge(27000 rows) and I am using excel to create a graph.
I have tried using line plots and bar charts(stacked) but couldn't. I am not sure if there is an easier way to do it .
View 4 Replies
View Related
Oct 7, 2006
id like a formula that enter "1" into a field if i start after 00:00 and before 03:59 am
so in d4 is my start time and M4 is where i want the formula to go
i tried this formula but did not work
=if(d4>="00:00",<"04:00",1,0)
View 9 Replies
View Related
Dec 20, 2006
Trying to get times worked in hours and mins, from a given start time to a given finished time.
I would like H18 to read 14
and H19 to read 13.5 and so on,
H23 and H24 are full 24 hour coverage,
but i get a 0, when using 08:00 - 08:00
as we only deal with a full or half hours, the figures should read whole numbers or half number as in th example above.
Also would like to total all hours worked in H28 to 117 as opposed to the figure given in the attached file.
DAY / DATESTARTENDHOURS
MON22-May200618:008:00 AM14:00
TUE23-May200617:006:30 AM13:30
WED24-May200617:307:30 AM14:00
THU25-May200618:007:30 AM13:30
FRI26-May200618:008:00 AM14:00
SAT27-May20068:008:00 AM0:00
SUN28-May20068:008:00 AM0:00
TOTAL2.875
Couldnt upload the file as it was 75kbs, a
View 9 Replies
View Related
Dec 8, 2009
How do you subtract start/end - dates/times in vba?
I know how to do it with formulas, but can't grasp it with vba. My range will always change, so I'm trying to avoid formulas (Cutting rows etc...)
I need Col D-Col B (end date-start date) and Col E - Col C (end time - start time)
Would doing the calculation on the userform be the best way, or trying to do the calculation with the ws code?
View 9 Replies
View Related
Aug 21, 2014
I am attempting to create a datasheet to track the actual time an agent takes a break or lunch. Here is an example of the data that I am using.
name Date Lunch Sch Brk1 Sch Brk 2 Actual Time
BrandonHolt8/19/201411:10 8:30 1:20 8:24:20 Break
BrandonHolt8/19/201411:10 8:30 1:20 11:42:34 Break
BrandonHolt8/19/201411:10 8:30 1:20 12:57:46 Lunch
Because I have two break times, possibility of more depending on the agent, how do i create a formula that will look at this data, take the schedule time for say break one and only apply that against the time that is more in line with the closest actual time?
View 1 Replies
View Related
Jan 21, 2008
I have a spreadsheet with 4 columns - Start Date, Start Time, End Date and End Time. In the 5th column, I need to fill in the "Duration" which is calculated as follows :- Duration = (End Date,End Time) - (Start Date,Start Time)
View 2 Replies
View Related
Mar 3, 2014
We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
View 3 Replies
View Related
May 23, 2006
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated.
Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details.
I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly?
Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file?
attached is book.xls and sales summary.xls for reference.
View 3 Replies
View Related
