• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating difference between two dates/times by business hours

P

Pitcher

Member
Hi guys,

I would like some help with regard to my excel requirement.

Within my excel I have a creation date in the following format 20/08/2014 and what I would like to calculate is the difference between it and the delivery date (same format) by number of hours. The issue I have is that there can be multiple delivery dates with the same ID and I would like to only calculate the difference between creation dates and first (most recent) delivery date.

The complex piece of this is that I would like to calculate the difference between the two dates in hours and also calculate the difference between the two dates using business hours. Business hours equals 09:00 - 17:30.

I have attached some sample data with some guidelines. If someone could help me on this is would be much appreciated.

Pitcher,
Andrew
 

Attachments

  • Chandoo Exmaple File.xlsx
    217 KB · Views: 19
Hi Pitcher -

Check the attached...hope this helps..

Note this is with the assumption that Weekends are off...
 

Attachments

  • Chandoo Exmaple File.xlsx
    322.3 KB · Views: 37
Pitcher said:
Hi guys,

I would like some help with regard to my excel requirement.

Within my excel I have a creation date in the following format 20/08/2014 and what I would like to calculate is the difference between it and the delivery date (same format) by number of hours. The issue I have is that there can be multiple delivery dates with the same ID and I would like to only calculate the difference between creation dates and first (most recent) delivery date.

The complex piece of this is that I would like to calculate the difference between the two dates in hours and also calculate the difference between the two dates using business hours. Business hours equals 09:00 - 17:30.

I have attached some sample data with some guidelines. If someone could help me on this is would be much appreciated.

Pitcher,
Andrew
Click to expand...
Pitcher said:
Hi guys,

I would like some help with regard to my excel requirement.

Within my excel I have a creation date in the following format 20/08/2014 and what I would like to calculate is the difference between it and the delivery date (same format) by number of hours. The issue I have is that there can be multiple delivery dates with the same ID and I would like to only calculate the difference between creation dates and first (most recent) delivery date.

The complex piece of this is that I would like to calculate the difference between the two dates in hours and also calculate the difference between the two dates using business hours. Business hours equals 09:00 - 17:30.

I have attached some sample data with some guidelines. If someone could help me on this is would be much appreciated.

Pitcher,
Andrew
Click to expand...
 
Asheesh said:
Hi Pitcher -

Check the attached...hope this helps..

Note this is with the assumption that Weekends are off...
Click to expand...

Asheesh,

This is awesome, thank you so much for your help.

Two things I am hoping you can further help me with:
1). Could you include a column that calculates the time difference creation and delivery in regular hours too?
2). In some cases you will see a delivery date which from a date/time perspective comes before the delivery date (impossible). This is a glitch in my data but for those examples is it possible to set the time difference to 0 for both business hours and regular hours?

Many thanks,
Andrew
 
Thank you for your feedback...

Check the attached...and let me know if u were looking for something else..
 

Attachments

  • Chandoo Exmaple File.xlsx
    304 KB · Views: 26
Hey Asheesh,

Apologies for my delayed response. The updated file is perfect, thank you.

I have uploaded a similar file, with a very similar task. The difference in the task is captured within the file but essentially the only difference in this file/task is that under each line item or ID I would like to capture the first touch/activity but for a specific Rep. There can be multiple reps working each ID but I would like to capture only the assigned reps first touch/activity per ID. The file uploaded contains only one name but the master file contains many.

I hope this makes sense?

Thanks in advance,
Pitcher
 

Attachments

  • Chandoo File v2 (Autosaved).xlsx
    402.8 KB · Views: 8
Hi Andrew,

Thank you for the feedback...

Regarding your query, can you please add couple of more reps to your data...and share couple of examples with the expected result..
 
Hi Asheesh,

I have now included two additional reps. I don't have examples of expected results unfortunately. Once I have the formulae/logic in place what I hope to then have is the time to touch/first activity per each line item or ID.

Does that make sense?

Cheers,
Andrew
 

Attachments

  • Chandoo File v2 (Autosaved).xlsx
    814.9 KB · Views: 10
Hey...There are several tabs in the attached..can you tell me where do you want the output and from which tab & which column...just trying to get my head around the data...

I may have some more questions while working through..so bear with me..
 
Hey Asheesh,

Using the input from the yellow tabs I would like to see the output in the green tabs.

In the “Pushlead Contacts” tab
· Column K - I would like to establish the time and date of the first activity from the “activities on contacts” tab using the contact ID and assigned name as the common denominator

· Column L – I would like to establish the time difference in Hours and minutes (business hours, 9-5:30 excluding weekends) from the created time (column G) and First touch on task (column K)

· Column M – I would like to establish the first activity type from the “activities on contacts” tab column G using the contact ID and assigned name as the common denominator

In the “Pushlead Lead” tab

· Column K - I would like to establish the time and date of the first activity from the “activities on leads” tab using the lead ID and assigned name as the common denominator

· Column L – I would like to establish the time difference in Hours and minutes (business hours, 9-5:30 excluding weekends) from the created time (column G) and First touch on task (column K)

· Column M – I would like to establish the first activity type from the “activities on leads” tab column G using the lead ID and assigned name as the common denominator

Hope this makes sense

Cheers,
Pitcher
 

Attachments

  • Chandoo File v3.xlsx
    815.1 KB · Views: 26
Thanks Asheesh!

As I will be adding in thousands of rows of additional data, would it be possible to write the formulae for an entire column as opposed to the specified sample data I provided?

Cheers,
Andrew
 
When I try to extend the range the formale changes where I cannot add back in the parenthesis/brackets at each end.
 
Andy, you can change the range as per your requirement...I think I have currently used absolute and mixed refrences...But remember, your data set being huge will definitely result in latency...

So what I would suggest is to use table option under "Insert" tab and then "Tables" group..

Regarding the curly braces at the begining and at the end...they must not be entered manually...use CTRL + SHIFT + ENTER and they shall automatically appear...

Let us know if you still face any issues...
 
Thanks Ashessh,

I wonder is there an alternative way to achieve the same results. The data set is massive and as a result Excel will not process the formulae.

If the earliest/first created date for each contact and Lead ID was included in a new column beside each row item, could a conditional vlookup be run to achieve the same result (first touch)? Perhaps this was be less intense on Excel?
 
Hi Asheesh,

The latest file you sent me on Tuesday was great.

I am hoping to update the existing formulae for "first touch" and "activity type" to only capture the first touch and activity type post the creation date.

Don't think the "time till first touch (bus hours)" formulae needs to be updated?

Is that possible?

Really appreciate your help on this.
Pitcher
 
Hi Andy -

Thank you for the feedback...first of all sorry for replying late as I have been little too busy offlate..

Now if I understand you correct - Let's take an example of "Pushlead Lead" tab..row number 778.

The first touch on task date is 8/19/2014 and the creation date is 8/20/2014..so, ideally the first touch date should be after 8/20/2014..now, we would want the date to pop up only if it is post 8/20/2014..however, in this case there is no such date available for this activity..so we shall leave it blank..

Basis your feedback, I shall make the necessary changes..
 
Hey Asheesh,

The example you provided is correct. What I want to capture is the time of the first touch post the creation date. Should there be none then please leave blank.

Same principle applies for the "activity type".

Is it possible to count the number of touches on each ID using the same principle. Where the ID and assigned name are the same, count the number of touches post creation date?

Thanks so much!
Andrew
 
yes it is all possible - however, I would suggest you to use pivot table to count the number of touches post creation date..because using more and more formulae would result in latency...let me know how do you wanna go about it..
 
Thanks Asheesh,

When counting the number of touches I used a pivot table and then worked a sumif formulae off it. The piece I couldn't incorporate was the post creation date piece.

If you have time, it would be great to include any alternatives to the using the array formulae in there at present. As you mentioned, it kills excels capacity. Happy to help in any way I can to find an alternative

Again, I really appreciate your time on this.

Cheers,
Andrew
 
Hi Asheesh,

Is there a way I could use pivot tables for each field that I am trying to calculate? If so I would be very grateful if you could show me how this can be done as this would allow me refresh the file much quicker than is currently possible using the array formulas.

Cheers,
Andrew
 
Hi @Hui

@Asheesh who has helped me enormously so far I know is busy. Hoping you had could help me or point me in the right direction regarding my file and task.

Under pressure to find a robust solution (should there be one) so would really appreciate any help on this. If any additional info is required I am happy to provide...

Thanks,
Andrew
 
Hi Andy,

Apologies...I have been able to breathe after a few days today...Looked into your query..and tried to incorporate the changes as suggested...now the formulae are dynamic..all you need to do is to append the data..

Regarding your query to simplify or can we reduce latency..I dont think all the things that you are looking for can be done using pivot..but a few can be...however, you treat this as an interim solution..will definitely look into it once I am at complete peace..moreover, you have asked Hui to look into it..I am sure..he will have a better solution...

Hope this helps...
 

Attachments

  • Chandoo File v2 (Autosaved).zip
    666.5 KB · Views: 20
Asheesh,

Thanks again for your help on this!

All the formulae in place now returns exactly what I want. If over the course of the next day or two you get a chance to somehow achieve the same results in a less excel strenuous manor (pivot tables or otherwise) that would be awesome! This would then allow me load in more and more data over time.

Cheers,
Andrew

@Hui
 
You must log in or register to reply here.
Back
Top