Calculating difference between two dates/times by business hours
- Thread starter Pitcher
- Start date
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
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
Asheesh
Excel Ninja
Select any cell of the table and Press ALT + J + G
I recommed to go through the below link...it explains the use of tables..
https://www.ablebits.com/office-addins-blog/2011/09/23/excel-tables-ranges/
I recommed to go through the below link...it explains the use of tables..
https://www.ablebits.com/office-addins-blog/2011/09/23/excel-tables-ranges/
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
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
NARAYANK991
Excel Ninja
Hi Andrew ,
I can 'suggest' Luke ! Though he may not take kindly to my suggestion.
Narayan
I can 'suggest' Luke ! Though he may not take kindly to my suggestion.
Narayan
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
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.
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.
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
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
NARAYANK991
Excel Ninja
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
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