+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Forum Contributor
    Join Date
    08-12-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    133

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-12-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2007 2010 2013
    Posts
    132

    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...........
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-12-2013
    Location
    India
    MS-Off Ver
    Excel 2013
    Posts
    133

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

    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

  4. #4
    Forum Contributor
    Join Date
    11-12-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2007 2010 2013
    Posts
    132

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Calculate number of hours from Start date and time and End date and time
    By sathyasun in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-28-2013, 01:04 AM
  2. Calculation of time difference between the Start date & time & End Date & time
    By Harry Jones in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 12-17-2012, 03:54 PM
  3. [SOLVED] Calculate turnaround time for 9pm-6am working time & Start/End date may be on weekend
    By Wauiwa in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-26-2012, 02:36 PM
  4. Replies: 1
    Last Post: 03-31-2011, 05:03 PM
  5. Calculating days & time left from start date/time to end date/time
    By marie in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2005, 10:40 AM

Bookmarks

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