Formula to Calculate Total time spent between Start Date & Time and End Date & Time

Hello Guys,

I need a formula (in Column G) which calculate the Total TAT time used between Start Date & Time (in Column B) and End Date & Time (in Column F) while considering the office working hours only.

Office Working Hours on different working days and Company Holiday list is present in "Holiday List" sheet.

The sample file is attached for your reference.

Thanks

Hi Manish,

I have prepared formula for you pls check and let know in case any thing more needed...........

Hello rishi,

Thanks for reply.

You had insert th formula in Column C, D and G. Please help me in understand how it works.

I need a TAT calculation formula in column G only (considering the Working hours, days and holidays present in Holiday List).

Thanks

sorry to reply you so late,

any ways m just considering in the tab "HolidayList" in column E you have only these holiday and work hrs for monday till friday is 9 hrs as ( 10 am till 7 pm) and for Saturday hrs are 3 hrs ( 9 am to 12 pm )


so now in sheet data you need to enter first ID in column e then there respective time login and time logout in column B and coumn F and then drag the formula in C and D

if data return the value yes it means particular ID spend there respective hrs ( for mon - fri 9 hr and if it is sat then for sat 3 hrs) and if in next column value returns as no that means particular ID could not maintain the timing (for mon -fri less then 9 hr and if it is sat then less then 3 hr )

and if it returns As Holiday that mean on particular day was a holiday.

( note if you like to increase the holiday date range u can also do that if you want pls let me know will help you)
many thanks

Rishi