• 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

Ashessh,

Unfortunately when I load more data into the tables it falls over. Is it possible to replicate the formulae without the use of the tables?

Ultimately it would be awesome to somehow have something automated/robust that returns the same data whilst allowing more data be loaded.

Cheers,
Andrew
 
Thanks Asheesh.

Like you said the array formulas are causing too much latency still for this to be a viable solution. Great for smaller quantities of data though.

Is there anyone I can reach out to who may also be able to help? Tried reaching @Hui but not sure I am doing it correctly...

Cheers,
Andrew
 
Hi @NARAYANK991 ,

Thanks for jumping in here. @Luke M if you had time to look at this I would very much appreciate it.

I understand that a robust alternative to the approach within the file will take some time but if any of you guys could help it would be awesome. The issue I have is latency, the formulae all work and provide what I need. @Asheesh has done an awesome job so far!

Thanks,
Andrew
 
No worries @Narayan, glad to help. :)

Hi Pitcher,
I think we can reduce a lot of calculations if instead of having all our formula columns figure out where the first touch is, we have a single column do that work. In the attached, I do this in column K. For your final version, you can hide column K so as not to distract user.

Once you know which row to be looking at (found w/o arrays), your other formulas become much less intensive.

The other trick I did was create a lookup helper column in source tables. By concatenating the columns, we no longer need an array in downstream tables.
 

Attachments

  • Business Hours.zip
    631.3 KB · Views: 9
Hey Luke,

Appreciate the help, this should hopefully help the latency. Before I load in some more data...

So just two things:
1). there are one or two anomalies, Row 5 (has a first touch date but there are no touches), Row 317 (there are 4 touches but the corresponding activity and time to touch etc was not captured. Can this be resolved?
2). Can you please update the "pushlead Lead" tab as per the "pushlead contacts" tab

Cheers,
Pitcher
 
Hi Andrew ,

I find that one entry :

00360000013V3Jx

does not figure anywhere in the output ; is this correct ?

When I download the file , I get a file corrupted message , and this may be the reason ; can you confirm ?

If it is so , can you send me your good file by email to :

narayank1026 [at] gmail [dot] com

Narayan
 
Back
Top