RarelyGraceful
New Member
- Joined
- Oct 3, 2012
- Messages
- 4
Good afternoon,
I am trying to get the average time it takes for employees to be ready/available for their shift, with a separate column showing the duration (minutes and seconds) since some people understand that better. However, anything over 20 minutes before or after the shift begin time needs to be excluded, because that means they were busy with a supervisor or something that prevented them from being ready quickly, or they came in early for some reason. I'm trying to get the true average of how long it typically takes them. Each shift has a different start time, so I have made a column with the shift begin time, as well as a column with the shift begin time, plus 20 minutes. I was thinking that I could use those times to make a formula that would only get the data inclusive between those times, but I haven't been able to make it work. I'm also running into the problem where I get the #DIV/0 error. I am using data that I have exported from a database and I have no control over if the information is entered correctly or not, so I am having to create formulas to grab only the information I need.
Also, some employees get here early, get everything checked out, and are available before their shift begin time. These averages will need to have a negative sign (-) in front of them to show they were ready before their shift, and I have been manually highlighting them yellow. I don't know if there is a way to have it automatically do that or not.
What I have been doing so far:
1. Column C: The actual start time of the employees should have only one time in the cell, however, for some reason, in some cells more than one time has been entered. To correct this, I have been using the REPLACE formula so I can extract the first time that is in the cell. I used 99 as the number of characters to replace since there may be a time entered twice, three times, or more. 99 covers it all. The formula I've been using is: =REPLACE($B2,13,99,"")
2. Column D: This may be a total waste, but I haven't been able to get Excel to calculate formulas if I leave the time in the hh:mm:ss format, so the next thing I do is use the TIMEVALUE formula. I have included the IFERROR formula in this step because some cells are empty, which would result in an error. The formula I've been using is: =IFERROR(TIMEVALUE($C2),"")
3. Column G: This is where I start the AVERAGEIFS, to get the average time the employees are ready (the actual time, such as 7:05:32 AM) but cannot get it to work correctly. This is where I need it to average the times by employee number, within 20 minutes before or after their shift begin time.
4. Column K: The final step is to get the average time in minutes and seconds it takes the employees to get ready, including employees that are ready before their shift begin, showing the time as a negative number. The only problem I have run into is that I don't have anything in this formula for time duration that crosses over midnight. The formula I've been using is: =IF($G2<$H2,TEXT($H2-$G2,"-h:mm:ss"),TEXT($G2-$H2,"h:mm:ss"))
I've already exported this data within a certain date range. For example, I am working on the month of September's data. The only two columns that have the exported data are columns A and B with the Employee's Number and the Actual Start time. Please keep in mind that some rows are blank, where information was not entered, and the blank cells will need to be ignored.
In column F, I have listed the employees by their number, in numerical order. Columns F through K will be the end result. This is a very small portion of the actual data, just to give you an idea of what I'm trying to accomplish.
An example of my spreadsheet is below. The second and third column have the imported data. The issues I'm having, I will explain by the numbered rows.
· Rows 1 and 7: Too many times are in that cell, so I replaced the text so I would have only one time to work with per cell.
· Row 1: The time is more than 20 minutes past the shift begin time, so it needs to be excluded. Only times inclusive between 20 minutes before or after the shift begin time should be included in the average.
· Row 2: Employee 360 was ready before the shift begin time, which is typical, and would result in a negative number, which I need to have a negative sign and highlighted in yellow.
· Row 3: There was not a time listed, which in turn creates the #DIV/0 error in the average. I need the blank/empty cells to be ignored.
· Rows 5 and 6: This shift begins at midnight. I need a formula that will calculate the times correctly IF it crosses over midnight. Not all shifts will have times that will cross over midnight, but some will.
· Row 10:The time is more than 20 minutes before the shift begin time, so it needs to be excluded. Only times inclusive between 20 minutes before or after the shift begin time should be included in the average.
[TABLE="class: grid, width: 1200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Employee[/TD]
[TD]Actual Start[/TD]
[TD]Act Start Replaced[/TD]
[TD]Time Value[/TD]
[TD][/TD]
[TD]Employee[/TD]
[TD]Avg. Time[/TD]
[TD]Shift Begin[/TD]
[TD]20 Mins. Before[/TD]
[TD]20 Mins. After[/TD]
[TD]Avg. T in Mins.[/TD]
[/TR]
[TR]
[TD]1.[/TD]
[TD]360[/TD]
[TD]11:05:41 AM 3:51:39 PM 3:54:11 PM[/TD]
[TD]11:05:41 AM[/TD]
[TD]0.462280093[/TD]
[TD][/TD]
[TD]350[/TD]
[TD]#DIV/0![/TD]
[TD]12:05:00 AM[/TD]
[TD]11:45:00 PM[/TD]
[TD]12:25:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.[/TD]
[TD]360[/TD]
[TD] 5:57:19 AM[/TD]
[TD] 5:57:19 AM[/TD]
[TD]0.248136574[/TD]
[TD][/TD]
[TD]351[/TD]
[TD][/TD]
[TD]10:00:00 AM[/TD]
[TD]9:40:00 AM[/TD]
[TD]10:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.[/TD]
[TD]361[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]352[/TD]
[TD][/TD]
[TD]10:00:00 AM[/TD]
[TD]9:40:00 AM[/TD]
[TD]10:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4.[/TD]
[TD]361[/TD]
[TD] 6:08:40 AM[/TD]
[TD] 6:08:40 AM[/TD]
[TD]0.256018519[/TD]
[TD][/TD]
[TD]360[/TD]
[TD][/TD]
[TD]6:00:00 AM[/TD]
[TD]5:40:00 AM[/TD]
[TD]6:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5.[/TD]
[TD]350[/TD]
[TD] 12:09:56 AM[/TD]
[TD] 12:09:56 AM[/TD]
[TD]0.006898148[/TD]
[TD][/TD]
[TD]361[/TD]
[TD][/TD]
[TD]6:00:00 AM[/TD]
[TD]5:40:00 AM[/TD]
[TD]6:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6.[/TD]
[TD]350[/TD]
[TD] 11:52:44 PM[/TD]
[TD] 11:52:44 PM[/TD]
[TD]0.994953704[/TD]
[TD][/TD]
[TD]362[/TD]
[TD][/TD]
[TD]6:30:00 AM[/TD]
[TD]6:10:00 AM[/TD]
[TD]6:50:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7.[/TD]
[TD]351[/TD]
[TD] 10:35:00 AM 6:19:59 PM[/TD]
[TD] 10:35:00 AM[/TD]
[TD]0.440972222[/TD]
[TD][/TD]
[TD]363[/TD]
[TD][/TD]
[TD]6:30:00 AM[/TD]
[TD]6:10:00 AM[/TD]
[TD]6:50:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8.[/TD]
[TD]351[/TD]
[TD] 10:11:13 AM[/TD]
[TD] 10:11:13 AM[/TD]
[TD]0.424456019[/TD]
[TD][/TD]
[TD]364[/TD]
[TD][/TD]
[TD]7:00:00 AM[/TD]
[TD]6:40:00 AM[/TD]
[TD]7:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9.[/TD]
[TD]352[/TD]
[TD] 10:07:04 AM[/TD]
[TD] 10:07:04 AM[/TD]
[TD]0.421574074[/TD]
[TD][/TD]
[TD]365[/TD]
[TD][/TD]
[TD]7:00:00 AM[/TD]
[TD]6:40:00 AM[/TD]
[TD]7:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10.[/TD]
[TD]352[/TD]
[TD] 9:05:04 AM[/TD]
[TD] 9:05:04 AM[/TD]
[TD]0.378518519[/TD]
[TD][/TD]
[TD]366[/TD]
[TD][/TD]
[TD]7:30:00 AM[/TD]
[TD]7:10:00 AM[/TD]
[TD]7:50:00 AM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
*I am using Excel 2007*
Any help would be greatly appreciated! I have been having to get rid of erroneous data and blank/empty cells manually for months. I can't figure out a solution.
Thanks!
RarelyGraceful
I am trying to get the average time it takes for employees to be ready/available for their shift, with a separate column showing the duration (minutes and seconds) since some people understand that better. However, anything over 20 minutes before or after the shift begin time needs to be excluded, because that means they were busy with a supervisor or something that prevented them from being ready quickly, or they came in early for some reason. I'm trying to get the true average of how long it typically takes them. Each shift has a different start time, so I have made a column with the shift begin time, as well as a column with the shift begin time, plus 20 minutes. I was thinking that I could use those times to make a formula that would only get the data inclusive between those times, but I haven't been able to make it work. I'm also running into the problem where I get the #DIV/0 error. I am using data that I have exported from a database and I have no control over if the information is entered correctly or not, so I am having to create formulas to grab only the information I need.
Also, some employees get here early, get everything checked out, and are available before their shift begin time. These averages will need to have a negative sign (-) in front of them to show they were ready before their shift, and I have been manually highlighting them yellow. I don't know if there is a way to have it automatically do that or not.
What I have been doing so far:
1. Column C: The actual start time of the employees should have only one time in the cell, however, for some reason, in some cells more than one time has been entered. To correct this, I have been using the REPLACE formula so I can extract the first time that is in the cell. I used 99 as the number of characters to replace since there may be a time entered twice, three times, or more. 99 covers it all. The formula I've been using is: =REPLACE($B2,13,99,"")
2. Column D: This may be a total waste, but I haven't been able to get Excel to calculate formulas if I leave the time in the hh:mm:ss format, so the next thing I do is use the TIMEVALUE formula. I have included the IFERROR formula in this step because some cells are empty, which would result in an error. The formula I've been using is: =IFERROR(TIMEVALUE($C2),"")
3. Column G: This is where I start the AVERAGEIFS, to get the average time the employees are ready (the actual time, such as 7:05:32 AM) but cannot get it to work correctly. This is where I need it to average the times by employee number, within 20 minutes before or after their shift begin time.
4. Column K: The final step is to get the average time in minutes and seconds it takes the employees to get ready, including employees that are ready before their shift begin, showing the time as a negative number. The only problem I have run into is that I don't have anything in this formula for time duration that crosses over midnight. The formula I've been using is: =IF($G2<$H2,TEXT($H2-$G2,"-h:mm:ss"),TEXT($G2-$H2,"h:mm:ss"))
I've already exported this data within a certain date range. For example, I am working on the month of September's data. The only two columns that have the exported data are columns A and B with the Employee's Number and the Actual Start time. Please keep in mind that some rows are blank, where information was not entered, and the blank cells will need to be ignored.
In column F, I have listed the employees by their number, in numerical order. Columns F through K will be the end result. This is a very small portion of the actual data, just to give you an idea of what I'm trying to accomplish.
An example of my spreadsheet is below. The second and third column have the imported data. The issues I'm having, I will explain by the numbered rows.
· Rows 1 and 7: Too many times are in that cell, so I replaced the text so I would have only one time to work with per cell.
· Row 1: The time is more than 20 minutes past the shift begin time, so it needs to be excluded. Only times inclusive between 20 minutes before or after the shift begin time should be included in the average.
· Row 2: Employee 360 was ready before the shift begin time, which is typical, and would result in a negative number, which I need to have a negative sign and highlighted in yellow.
· Row 3: There was not a time listed, which in turn creates the #DIV/0 error in the average. I need the blank/empty cells to be ignored.
· Rows 5 and 6: This shift begins at midnight. I need a formula that will calculate the times correctly IF it crosses over midnight. Not all shifts will have times that will cross over midnight, but some will.
· Row 10:The time is more than 20 minutes before the shift begin time, so it needs to be excluded. Only times inclusive between 20 minutes before or after the shift begin time should be included in the average.
[TABLE="class: grid, width: 1200, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Employee[/TD]
[TD]Actual Start[/TD]
[TD]Act Start Replaced[/TD]
[TD]Time Value[/TD]
[TD][/TD]
[TD]Employee[/TD]
[TD]Avg. Time[/TD]
[TD]Shift Begin[/TD]
[TD]20 Mins. Before[/TD]
[TD]20 Mins. After[/TD]
[TD]Avg. T in Mins.[/TD]
[/TR]
[TR]
[TD]1.[/TD]
[TD]360[/TD]
[TD]11:05:41 AM 3:51:39 PM 3:54:11 PM[/TD]
[TD]11:05:41 AM[/TD]
[TD]0.462280093[/TD]
[TD][/TD]
[TD]350[/TD]
[TD]#DIV/0![/TD]
[TD]12:05:00 AM[/TD]
[TD]11:45:00 PM[/TD]
[TD]12:25:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2.[/TD]
[TD]360[/TD]
[TD] 5:57:19 AM[/TD]
[TD] 5:57:19 AM[/TD]
[TD]0.248136574[/TD]
[TD][/TD]
[TD]351[/TD]
[TD][/TD]
[TD]10:00:00 AM[/TD]
[TD]9:40:00 AM[/TD]
[TD]10:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3.[/TD]
[TD]361[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]352[/TD]
[TD][/TD]
[TD]10:00:00 AM[/TD]
[TD]9:40:00 AM[/TD]
[TD]10:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4.[/TD]
[TD]361[/TD]
[TD] 6:08:40 AM[/TD]
[TD] 6:08:40 AM[/TD]
[TD]0.256018519[/TD]
[TD][/TD]
[TD]360[/TD]
[TD][/TD]
[TD]6:00:00 AM[/TD]
[TD]5:40:00 AM[/TD]
[TD]6:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5.[/TD]
[TD]350[/TD]
[TD] 12:09:56 AM[/TD]
[TD] 12:09:56 AM[/TD]
[TD]0.006898148[/TD]
[TD][/TD]
[TD]361[/TD]
[TD][/TD]
[TD]6:00:00 AM[/TD]
[TD]5:40:00 AM[/TD]
[TD]6:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6.[/TD]
[TD]350[/TD]
[TD] 11:52:44 PM[/TD]
[TD] 11:52:44 PM[/TD]
[TD]0.994953704[/TD]
[TD][/TD]
[TD]362[/TD]
[TD][/TD]
[TD]6:30:00 AM[/TD]
[TD]6:10:00 AM[/TD]
[TD]6:50:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7.[/TD]
[TD]351[/TD]
[TD] 10:35:00 AM 6:19:59 PM[/TD]
[TD] 10:35:00 AM[/TD]
[TD]0.440972222[/TD]
[TD][/TD]
[TD]363[/TD]
[TD][/TD]
[TD]6:30:00 AM[/TD]
[TD]6:10:00 AM[/TD]
[TD]6:50:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8.[/TD]
[TD]351[/TD]
[TD] 10:11:13 AM[/TD]
[TD] 10:11:13 AM[/TD]
[TD]0.424456019[/TD]
[TD][/TD]
[TD]364[/TD]
[TD][/TD]
[TD]7:00:00 AM[/TD]
[TD]6:40:00 AM[/TD]
[TD]7:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9.[/TD]
[TD]352[/TD]
[TD] 10:07:04 AM[/TD]
[TD] 10:07:04 AM[/TD]
[TD]0.421574074[/TD]
[TD][/TD]
[TD]365[/TD]
[TD][/TD]
[TD]7:00:00 AM[/TD]
[TD]6:40:00 AM[/TD]
[TD]7:20:00 AM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10.[/TD]
[TD]352[/TD]
[TD] 9:05:04 AM[/TD]
[TD] 9:05:04 AM[/TD]
[TD]0.378518519[/TD]
[TD][/TD]
[TD]366[/TD]
[TD][/TD]
[TD]7:30:00 AM[/TD]
[TD]7:10:00 AM[/TD]
[TD]7:50:00 AM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
*I am using Excel 2007*
Any help would be greatly appreciated! I have been having to get rid of erroneous data and blank/empty cells manually for months. I can't figure out a solution.
Thanks!
RarelyGraceful
