#1
|
|||
|
|||
Time Duration
I need help please. I need to calculate a column of data reflecting time duration. The buckets I need are 0 <=8, 8 <=16, 16 <= 24 My data is in one column and may be a duration of 0:47:08 , or 1:17:24 , etc. So, essentially the calculation would be 0 hrs to <=8 hrs I would then create a pivot table to sum or count each of those values per day. For example, out of 2000 jobs 345 would be between 0-8 hours, 444 between 8 and 16 hours, etc. |
#2
|
||||
|
||||
Hi
try using the following in a helper column =FLOOR(A1,1/3) and format as time All results as 00:00:00 are in bin1, 08:00:00 in bin 2, etc... and then create your pivot
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Thank you Peco. The formula is close but not exactly what I need. For example, the lines below are returning the following results:
2014-11-22 19.00.43 2014-11-22 19.08.18 0:07:35 8:00 2014-11-22 22.09.56 2014-11-22 22.09.56 0:00:00 8:00 2014-11-23 02.00.19 2014-11-23 02.06.28 0:06:09 8:00 2014-11-22 23.00.58 2014-11-22 23.14.29 0:13:31 8:00 2014-11-23 19.00.15 2014-11-23 19.05.12 0:04:57 8:00 2014-11-24 19.00.35 2014-11-24 19.08.32 0:07:57 8:00 Thank you. |
#4
|
|||
|
|||
Sorry, last email didn't transfer properly. Please see attachment.
|
#5
|
||||
|
||||
Could you please post an XL sheet?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
Sure. Thank you.
|
#7
|
||||
|
||||
Could you indicate manually in your sheet the expected results? It works OK for me but I probably don't get the point
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#8
|
|||
|
|||
Ok, so I added a few more lines of data. What I need is three different identifiers. We would like to know jobs that take 0-8 hours to run, 8-16 hours to run, or 16-24 hours to run. So, each line of data should be identified as one of those three timeframes.
Once I have the identifiers I can either do a sum or count in my pivot table. Thank you again. I appreciate your help with this. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
date & time of last time a recent file was used | pgeorgilas | Word | 1 | 10-31-2014 08:59 AM |
Time allotted minus time used with result in hour and minute | esther6086 | Excel | 2 | 04-29-2014 05:03 PM |
Project Duration for Part Time Resources | Kingrollo | Project | 5 | 11-20-2012 06:44 AM |
Change Resource Time vs. Duration when Updating Work column | lylel | Project | 3 | 07-23-2012 06:09 PM |
IE Object: Run-time problem (the link is not clicked in run-time but not in step-in | tinfanide | Excel Programming | 1 | 03-04-2012 12:05 AM |