Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-25-2014, 09:48 AM
cangelis cangelis is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Novice
Time Duration
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 11-26-2014, 05:46 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

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
Reply With Quote
  #3  
Old 11-26-2014, 06:00 AM
cangelis cangelis is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Novice
Time Duration
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 11-26-2014, 06:07 AM
cangelis cangelis is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Novice
Time Duration
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default

Sorry, last email didn't transfer properly. Please see attachment.
Attached Files
File Type: doc Time duration.doc (28.5 KB, 1 views)
Reply With Quote
  #5  
Old 11-26-2014, 12:33 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Could you please post an XL sheet?
Reply With Quote
  #6  
Old 11-26-2014, 12:37 PM
cangelis cangelis is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Novice
Time Duration
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default

Sure. Thank you.
Attached Files
File Type: xlsx duration times.xlsx (10.7 KB, 1 views)
Reply With Quote
  #7  
Old 11-27-2014, 04:50 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
Default

Could you indicate manually in your sheet the expected results? It works OK for me but I probably don't get the point
Reply With Quote
  #8  
Old 11-28-2014, 06:31 AM
cangelis cangelis is offline Time Duration Windows 7 64bit Time Duration Office 2010 64bit
Novice
Time Duration
 
Join Date: Jan 2014
Posts: 18
cangelis is on a distinguished road
Default

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.
Attached Files
File Type: xlsx duration times.xlsx (12.3 KB, 6 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Time Duration 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 08:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft