Complex - Calculate time between two dates - and exclude Specific dates

[Surya_Suresh]

Hello Team
I want a favor to help me out with the formula in Column F which will help me find revert time. Also, I have shifts scheduled so revert time should be calculated only within the given shift time and should exclude rest of time. It should also eliminate certain dates where it falls holiday
I have attached the sheet for your reference.

Your Support will be Highly appreciated.

 

[vletm]

Surya_Suresh​

I modified one of older sample which could work with Your data too.
Usage:
Modify data as needed.
Take care about "Inits"-sheet.
Press [ Do It ] to get results.
Ps. Macros have to be enabled.

 

[Surya_Suresh]

Thank you Vletm for helping and coming with nice solution. .
It worked well for me and I can see the required data coming by clicking on the DOIT Button. Its just I have converted the file from XLSX to XLSM

I have last couple of questions, It would be very helpful in getting support
if I create a new sheet copy in the workbook for another task how will macro work and link with Inits"-sheet. I need the same format with the same logic 2 more sheets

Also in the Calculation sheet is it okay if I add a Row from the top and a Column from the right for adding further details? Will there b any impact on Macro that you have created. or is there a way that Macro can b dynamic.


Regards
Surya

 

[vletm]

Surya_Suresh​

#1 You could use it with .xls or .xlsm or .xlsb -versions.
#2 You could duplicate that file and use each file separate ... (have three files) or there have to make modifications with code.
#3 That table have to start from same cell.
You can add as many rows to that table.
( ... if You'll have less rows than with previous [ Do It ] there will be a challenge - it won't clean Revert-values. )
You can add more columns to right. (Received, Sent & Revert (D,E & F) columns have to be in same place)
#4 What do You mean about dynamic?

 

[Surya_Suresh]

Dear Vletm,
Thank you for your swift reply and explanation to the point.
I will check the above points and get back to you in case you need any clarification.
I meant regarding Dynamic in case If I add the Row on the top and Macro starts from the Top cell where it data had originally started then there would be chances of overlapping the data when I run the Macro, Hence I wanted the remove the confusion.

For now, all the questions are answered Thank you

Regards,
Surya

 

[Surya_Suresh]

Dear Vletm
Please be informed that the file that you had sent is working perfectly fine with click.
However, I tried amending the data and adding worksheet to the exisiting file, however was not able to do
Appreciate if you could please help me with this.

If you could help me adding in module and giving refeering the name of the sheet that would be a great help.
In coming future If i have to add new work sheet then I can replicate by addding sheet and adding module.

Here is the file for your reference.

Sorry for additional work which i could have checked and given you earlier rather than now.

Looking to see positive response from you

Regards
Surya

 

[vletm]

Surya_Suresh​

I checked Your file (#6 reply) and noticed that You seems to skipped #4 reply.
I'm using only my file.
I modified calculation-sheet as You've done (which causes that [ Do It ] won't work as it should work).
I added there two more calculation sheets as You have done.
(no matter of names nor number of sheets (if bases with the 1st sheet).
You wrote something about Module ... where do You need something like that?
... Reread the sentence in brackets above

 

[Surya_Suresh]

Dear Vletm,
I did replied your comment on "You seems to skipped #4 reply"

Here is the comments for your visuals
Quote
I meant regarding Dynamic in case If I add the Row on the top and Macro starts from the Top cell where it data had originally started then there would be chances of overlapping the data when I run the Macro, Hence I wanted the remove the confusion.
Unquote

You wrote something about Module ... where do You need something like that? -
Create the file in Macro enabled, Insert the module, when writing the macro we need to add the name of the sheet and give reference formula to the sheet where name is mentioned.

this way it becomes dynmaic and if you enter n numbers of sheet we then just have to add module in VBA add code and change the name of the sheet to the one which we are using.

I am happy until it suffice the purpose. I will use the file and if i am stuck anywhere then will write back.

Thank you
Surya

 

[Surya_Suresh]

I have checked the utility and I am facing the below issue: 'The process is continuous updating the data in rows.'

1. In case the task is incomplete and if the Received or Sent date time is not updated or missing then Excel is not responding and at times excel is getting auto closed which increases high possibility of losing data. Because the data will increase day by day and it will act as a master file.
What if the data size has gone in thousands then it will create problems in maintaining the file.
Alternately, the utitliy can skip the data where either of data is not updated (Received / Sent) and calculate when both the data is updated

2. Also, another problem that I am observing is in case 10 rows are already updated with the Result. If i am adding data from 11 rows to 20 rows then it is showing as not responding or Excel is getting closed. the output should be calculated from 11 row onwards in case data is already updated until 10 row

Looking to see you reply and solution to the above points

Surya

 

[vletm]

Surya_Suresh​

>> "You seems to skipped #4 reply"
#3 That table have to start from same cell.
Means table's left top corner is fixed. ... or it would mean ameba-effect with any code.
You can add more columns to right. (Received, Sent & Revert (D,E & F) columns have to be in same place)
How else would I try to explain above?

>> Dynamic
Maybe You means that You would like to add there as many rows ...

>> Module
Why do You would like to do something manually? ... if it would work without later ... coding.

>> 1. In case the task ..
I have shifts scheduled so revert time should be calculated only within the given shift time and should exclude rest of time. It should also eliminate certain dates where it falls holiday
Have You've changed Your idea from the beginning ... ?

Complex - Calculate time between two dates - and exclude Specific dates​

Did Your sample file give a clear image - what else are You doing?

>> 2. Also, another problem ...
You're calling something with ... problem ... what is it?
You should able to send a file - to show - what is Your point now?

 

[Surya_Suresh]

Hello Vletm


>> "You seems to skipped #4 reply"
#3 That table have to start from same cell.
Means table's left top corner is fixed. ... or it would mean ameba-effect with any code.
You can add more columns to right. (Received, Sent & Revert (D,E & F) columns have to be in same place)
How else would I try to explain above? -
This was already explained by you and I have already got you, I have just mentioned to inform i have not ignored your msg. So please dont take it wrong or otherwise

>> Dynamic
Maybe You means that You would like to add there as many rows ...
>> Module
Why do You would like to do something manually? ... if it would work without later ... coding.
I have mentioned if my purpose is served that i am okay, however checked whether this could be possible option to explore or not.
Sheet has specific condition and if I have to amend then entire logic gets changed. hence i

>> 1. In case the task ..
I have shifts scheduled so revert time should be calculated only within the given shift time and should exclude rest of time. It should also eliminate certain dates where it falls holiday
Have You've changed Your idea from the beginning ...

Task and Shift are inter-lined with each other. If anyone is following up on any task is if the task is pending for some reason and Sent date will be incomplete. and If person click on DOIT button for the reason then should skip the row.
I have attached the file for your reference. Check Calculation 1 sheet for your reference

>> 2. Also, another problem ...
You're calling something with ... problem ... what is it?
You should able to send a file - to show - what is Your point now? - Sorry, I thought you had already created macro so you would be aware about the logic what i am taking. Had made a note of it.

Please see Calculation 2 - I have highlighted yellow color until DO it button is clicked and now when i have added the data after yellow button then it should calculate below the yellow data.



Regards
Surya

 

[vletm]

>> 1. In case the task ..
Do You mean that Your idea has changed?
Did You know that ... if the idea has changed ... that would mean in 90% of cases - start from zero of coding?
Now, I've gotten image that Your wish could solve other ways ... without that [ Do It ]-button.

>>>Sorry, I thought you had already created macro ...
Do above mean that You've skipped to test my previous file with details You've given?
That file has already needed macros which You've clearly written.
Some of Your changed ideas are still missing.

>> Do Your i have added the data ... actions mean that You have written something somewhere and pressed <ENT>?
or something else?
Do You have a plan to delete some rows?
>> Do You have an image, will there be more than 100 rows per sheet or how many (eg 500)?
All matters!

 

[vletm]

Surya_Suresh​

Here, without answers, things are handled as the questioner would do them.
This is my the newest version - take care.
There are none [ Do It ]-buttons.