Turn time based on Condition

[ssj011]

Good Day,

Need your assistance to get the formula to calculate the total turn-time taken by the user to perform the task. Below are the conditions for turn time calculations


1. We work in 24 by 7 shifts in weekdays (Monday to Friday )
2. Weekday shifts start at morning 06:30 AM and end on next day morning 6:30 AM
3.If Column C value is "Yes", then we work on weekends
4. Weekend shifts are 09:30 to 17:30
5. TAT = Load Date & Time - Complete Date & Time

Attached the sample data for your reference.

In the attached data, we have shown the day by day hours worked.


Regards
Sachin

 

[Faseeh]

Hi,

Please see this post by chandoo:

https://chandoo.org/wp/working-hours-formula/

 

[vletm]

ssj011
One sample version for testing ... press [ Do It ]
... this would need some modifications.
I left Your results for reference.

 

[ssj011]

ssj011
One sample version for testing ... press [ Do It ]
... this would need some modifications.
I left Your results for reference.

Thanks for the macro!!!

if there are scenarios where days are more than a week then how we can calculate the result.

Regards
Sachin

 

[vletm]

ssj011
hmm? ... press [ Do It ]
... or how would You need to do it?
Use this version ....

 

[ssj011]

Good Day,

Can we have formula instead of macro, as on a weekly basis I have 15000 lines for which I need to calculate TAT.

Regards
Sachin