Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-12-2015, 08:22 AM
ballst ballst is offline Macro needed for time calculation and auto insert Windows XP Macro needed for time calculation and auto insert Office 2003
Advanced Beginner
Macro needed for time calculation and auto insert
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default Macro needed for time calculation and auto insert

Hi,



Looking for some help with a macro for a huge batch of data I've got to plough through....

I've been tasked with calculating some task processing times. I'm therefore hopeful someone knows of a macro or two which could a) indentify a starting and ending point, b) insert a row after the end point and then c) calculate the time between start and end point. There is a further problem in that tasks started and completed overnight show as an error, so bonus gratitude if anyone knows how to counter that.

I've attached a small sample of data to show what I'm working with, highlighting the start point in green and the end point in red. Column E contains the data for the start and stop, so in this instance would be "NEWCASE" and "Notify Parties Application Received - Early Notification Letters Sent. Move on to Recommend Track"

For the example I have manually inserted the cell and calculation in the example data, but would be grateful for any pointers on how to automate that process as we have tens of thousands to get through.

Thank you for looking.


Ballst
Attached Files
File Type: xls ExampleData.xls (168.0 KB, 8 views)
Reply With Quote
  #2  
Old 05-16-2015, 08:07 PM
NoSparks NoSparks is offline Macro needed for time calculation and auto insert Windows 7 64bit Macro needed for time calculation and auto insert Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Ballst, with row 2 being empty, is it reasonable to think you want a blank line above each "NEWCASE" line ?

What is the correct Time Taken result that should be in cell C13 ...... 26 hours 8 minutes 54 seconds ?

What would the end point be for NEWCASE ---> LON/00AM/OLR/2014/0558
that starts on your line 952 ?

Last edited by NoSparks; 05-16-2015 at 10:32 PM. Reason: added third question
Reply With Quote
  #3  
Old 05-18-2015, 04:45 AM
ballst ballst is offline Macro needed for time calculation and auto insert Windows XP Macro needed for time calculation and auto insert Office 2003
Advanced Beginner
Macro needed for time calculation and auto insert
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
Ballst, with row 2 being empty, is it reasonable to think you want a blank line above each "NEWCASE" line ?

What is the correct Time Taken result that should be in cell C13 ...... 26 hours 8 minutes 54 seconds ?

What would the end point be for NEWCASE ---> LON/00AM/OLR/2014/0558
that starts on your line 952 ?
Hi NoSparks, thanks for the reply.

A blank line looks neat & would help define each task, so could be useful.

You are right that the correct time should be 26+ hours, I changed the time as my original sum calculation returned it as an error, so did 'cheat' and make a change.

For NEWCASE ---> LON/00AM/OLR/2014/0558 the end point would be
Critical Information Missing. Move on to Notify Parties Incomplete Application Received.
Reply With Quote
  #4  
Old 05-18-2015, 06:21 AM
NoSparks NoSparks is offline Macro needed for time calculation and auto insert Windows 7 64bit Macro needed for time calculation and auto insert Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Quote:
For NEWCASE ---> LON/00AM/OLR/2014/0558 the end point would be
Critical Information Missing. Move on to Notify Parties Incomplete Application Received.
This would indicate the end point for cases to be the first TASKCOMP in column D for each case.
Is this assumption correct and applicable to every case ?
Reply With Quote
  #5  
Old 05-18-2015, 07:22 AM
ballst ballst is offline Macro needed for time calculation and auto insert Windows XP Macro needed for time calculation and auto insert Office 2003
Advanced Beginner
Macro needed for time calculation and auto insert
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
This would indicate the end point for cases to be the first TASKCOMP in column D for each case.
Is this assumption correct and applicable to every case ?
Hmm, very nearly, but no I don't think that would always be the case.

The data I'm working with is a snapshot of all tasks completed between Jan and I think October. The data is then displayed by reference number with the first recorded task starting things off. In that circumstance it might mean that somewhere within the data a case doesn't start with a TASKCOMP.

I would also need to measure the time taken between other task stages, so these would not always end with a TASKCOMP either. My limited knowledge of these things suggests that column E is the driver, so I would want to select one type of audit note to start and then another to end.

For example on Line 37, we start with a NEWCASE but end on Line 46 with ONHOLD as there are a couple of extra tasks carried out.

Hope this makes sense.

Ballst
Reply With Quote
  #6  
Old 05-18-2015, 10:00 AM
NoSparks NoSparks is offline Macro needed for time calculation and auto insert Windows 7 64bit Macro needed for time calculation and auto insert Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Back to your original post:

a) identify a starting and ending point.... Excel can find anything it's asked to find if it exists. It's the user that must tell Excel what it's to look for or how to identify whatever it's looking for.

That's the part of your problem that's missing. I think your last post says that within each NEWCASE there are audit notes that would "pair up" to give start and end of tasks, but without Excel knowing this, no time taken can be calculated.

b) insert a row after the end point..... first you need to solve the identity problem then it's just a matter of .Offset(1, 0).EntireRow.Insert

c) calculate the time between start and end point. There is a further problem in that tasks started and completed overnight show as an error.... For cell C13 of your posted sheet the Time Taken =(B12+C12)-(B3+C3) formatted [h]:mm:ss
Reply With Quote
  #7  
Old 05-20-2015, 03:16 PM
ballst ballst is offline Macro needed for time calculation and auto insert Windows XP Macro needed for time calculation and auto insert Office 2003
Advanced Beginner
Macro needed for time calculation and auto insert
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
Back to your original post:

a) identify a starting and ending point.... Excel can find anything it's asked to find if it exists. It's the user that must tell Excel what it's to look for or how to identify whatever it's looking for.

That's the part of your problem that's missing. I think your last post says that within each NEWCASE there are audit notes that would "pair up" to give start and end of tasks, but without Excel knowing this, no time taken can be calculated.

b) insert a row after the end point..... first you need to solve the identity problem then it's just a matter of .Offset(1, 0).EntireRow.Insert

c) calculate the time between start and end point. There is a further problem in that tasks started and completed overnight show as an error.... For cell C13 of your posted sheet the Time Taken =(B12+C12)-(B3+C3) formatted [h]:mm:ss
NoSparks,

Cheers, re b & c. On point A, I want to use the data in column E, so I'll want to select any two data entries in that column, so for example.

Start "NEWCASE" End "Notify Parties Application Received - Early Notification Letters Sent. Move onto Recommended Track"

I would assume that info could be changed in the macro, depending what options I selected. How that all ends up in a macro is sadly beyond me though.
Reply With Quote
  #8  
Old 05-20-2015, 04:57 PM
NoSparks NoSparks is offline Macro needed for time calculation and auto insert Windows 7 64bit Macro needed for time calculation and auto insert Office 2010 32bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

ballst, please don't quote entire posts, it's not necessary.

I've attached the file I've been playing around with for the example data you posted.
The particular end point you specified seems to exist in every NEWCASE except for the one I asked about. Whether it's really what you would be looking for in all cases, I don't know.

Any way, have a look at the macros in this file, perhaps there is something you will be able to adapt.
Just hit Alt + F8 to bring up the macro dialogue and you can run the macros.

Good luck with this project
NoSparks
Attached Files
File Type: xls TimeTaken_01.xls (206.5 KB, 11 views)

Last edited by NoSparks; 05-20-2015 at 05:21 PM. Reason: added the Alt F8 part
Reply With Quote
  #9  
Old 05-21-2015, 03:54 PM
ballst ballst is offline Macro needed for time calculation and auto insert Windows XP Macro needed for time calculation and auto insert Office 2003
Advanced Beginner
Macro needed for time calculation and auto insert
 
Join Date: Dec 2014
Posts: 32
ballst is on a distinguished road
Default

NoSparks, wow thank you very much.

Seems to do the business, I'll play with it properly when I'm back in the office and try to adapt the macro.

Thansk again, Ballst
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro needed for time calculation and auto insert Preventing Auto Metadata - Help Needed! samcmakin Word 1 04-09-2015 11:48 PM
Macro needed for time calculation and auto insert How to prevent the auto-calculation of the '% Complete' field in MS Project Professional 2013? VivekG1 Project 1 02-06-2015 05:33 AM
'Monday to Friday' Date calculation help needed ballst Word 10 12-16-2014 09:51 AM
Macro Needed to bold specific lines and Macro to turn into CSV anewteacher Word VBA 1 05-28-2014 03:59 PM
Macro needed for time calculation and auto insert Macro Needed to Insert Asnwer to A Question in Multiple Choice Format Question rsrasc Word VBA 7 03-28-2014 12:28 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:23 AM.


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