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

1. ## 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

2. ## Re: Formula to Calculate Total time spent between Start Date & Time and End Date & Time

Hi Manish,

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

3. ## Re: Formula to Calculate Total time spent between Start Date & Time and End Date & Time

Hello rishi,

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

Thanks

4. ## Re: Formula to Calculate Total time spent between Start Date & Time and End Date & Time

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1