+ Reply to Thread
Results 1 to 4 of 4

Calculate Shift Hours (Date/Time difference in an interval)

  1. #1
    Registered User
    Join Date
    02-06-2016
    Location
    India
    MS-Off Ver
    Office 2010
    Posts
    3

    Calculate Shift Hours (Date/Time difference in an interval)

    Hi,

    We have a situation where we need to find "number of hours" spent by each employee on "a particular date" but in between "a fixed interval".

    Fixed Interval - Daily from 7:30 AM to 4:30 PM
    We have list of employees and dates for the data.
    Shifts can spillover several days, so solution should let us know that employee's hours on each date in fixed interval (below line 1)
    An employee can have several shifts in a day i.e. 4 shifts for 2 hours each (below line 3 and 4).

    Emp Start Date/Time End Date/Time
    X1 7/1/2020 6:00:01 AM 7/4/2020 2:00:00 AM
    X2 6/6/2020 8:35:09 AM 6/6/2020 3:35:09 PM
    X3 5/3/2020 6:30:00 AM 5/3/2020 7:50:00 AM
    X3 5/3/2020 8:30:00 AM 5/3/2020 2:30:00 PM

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,921

    Re: Calculate Shift Hours (Date/Time difference in an interval)

    Perhaps the following will help.
    1. Add three columns to the raw data
    The first column calculates the time on the first day using:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The second column calculates the time on the last day using: =IF(OR(INT(B3)=INT(C3),MOD(C3,1)<=B$1),0,MIN(MOD(C3,1)-B$1,C$1-B$1))
    The third column calculates the time for the days between using: =IF(INT(B3)=INT(C3),0,(INT(C3)-INT(B3)-1)*(C$1-B$1))
    2. Results are reported in a pivot table where Emp is placed in the Row area and a calculated field is placed in the values area.
    The formula for the calculated field is: ='First Day'+'Last Day' +Between
    For future reference, you will usually get faster results if you upload a sample .xlsx file (instructions are in the banner at top of the page)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    02-06-2016
    Location
    India
    MS-Off Ver
    Office 2010
    Posts
    3

    Re: Calculate Shift Hours (Date/Time difference in an interval)

    Thanks JeteMc. The formula just saved a lot of manual efforts at my end. Cheers.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,921

    Re: Calculate Shift Hours (Date/Time difference in an interval)

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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] how to calculate the difference between two dates&time within certain hours
    By Qhamzarul15 in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 01-12-2020, 03:41 PM
  2. Replies: 1
    Last Post: 07-13-2019, 01:12 PM
  3. Replies: 0
    Last Post: 06-14-2014, 02:48 PM
  4. Replies: 1
    Last Post: 09-13-2012, 05:50 AM
  5. Replies: 0
    Last Post: 09-13-2012, 05:28 AM
  6. Replies: 3
    Last Post: 12-23-2010, 04:46 PM
  7. calculate difference in time to hours
    By Chris in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-18-2005, 03:06 PM

Tags for this Thread

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