+ Reply to Thread
Results 1 to 4 of 4

Late/Tardiness Hours calculation per interval

  1. #1
    Registered User
    Join Date
    02-14-2020
    Location
    Quezon City Philippines
    MS-Off Ver
    MS Office 2013
    Posts
    2

    Late/Tardiness Hours calculation per interval

    Hey guys, can someone help me with this. What I wanted to know is what formula to use to calculate the late/tardy HOURS per 30 minute interval instead of just FTE.


    So I have a rawdata where I am putting the names with start and end time for their lates/tardiness. Then there is another tab where I put all the intervals for the day (30 minute increment) and just manually calculate the late hours to their corresponding intervals. The goal is to generate a formula to calculate these late hours and show exactly the same result with just manual calculations.
    Attached Files Attached Files

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

    Re: Late/Tardiness Hours calculation per interval

    I imagine that you are hoping for a single formula solution, and perhaps someone will be able to oblige, however until then perhaps the following will help.
    This proposal employs numerous helper columns on the Rawdata sheet.
    Row 1 is populated using 30 minute time intervals from 12:00 AM to 11:30 PM
    Rows 2:10 are populated using: =IF(MOD($B2,1)-E$1< TIME(0,1,0),MAX(0,MIN($C2-$B2,MOD($C2,1)-E$1,F$1-E$1)),0)
    Note that zero values are hidden using conditional formatting.
    On the Overview sheet the calculation per interval is populated using: =SUMPRODUCT((Rawdata!E$2:AZ$10)*(INT(Rawdata!B$2:B$10)=INT(A2))*(ABS(Rawdata!E$1:AZ$1-MOD(A2,1))< TIME(0,1,0)))/TIME(1,0,0)
    Note that the calculation take into account the date as well as the time which may be tested by changing the date in cell A16 to 2/14/2020
    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
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: Late/Tardiness Hours calculation per interval

    Hi, there

    I convert the rawdata into a table named "ROSTER" and the area with intervals into another table for simplicity.


    1. Match each interval up with the rawdata with MATCH func

    2.If matched up, the time difference is calculated using a subtraction

    3.If no matchup available, test if this interval falls into a span of time in the ROSTER table

    4.If so, simply calculate the time difference between the end time and the interval.

    (really wierd as long as I paste formula, submitting the reply fails...)

    pls refer to the complete formula in the attached file.
    Attached Files Attached Files
    Last edited by ThxAlot; 02-21-2020 at 08:55 PM.

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

    Re: Late/Tardiness Hours calculation per interval

    @ThxAlot, submitting fails because of the < immediately followed by ROSTER, something to do with Sucuri thinking it is a hyperlink I believe. If you put a space between the symbol and the text then the array entered formula will display as shown below.
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    By the way, Nice Solution!

+ 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. Calculating Late/Tardy per interval to FTE equivalent
    By paumanuel in forum Excel General
    Replies: 2
    Last Post: 02-14-2020, 11:48 AM
  2. Replies: 3
    Last Post: 02-03-2020, 03:08 AM
  3. Time formula for late work hours
    By Aspmyra in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-27-2019, 04:10 AM
  4. calculate the OT and Late - 10.5 working hours
    By concepcion.jensen in forum Excel General
    Replies: 19
    Last Post: 02-03-2017, 06:36 AM
  5. [SOLVED] Getting Late and Undertime Hours
    By princess_jay in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-26-2015, 01:48 PM
  6. Help me on how to calculate late hours
    By wawasimark in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-13-2013, 04:25 AM
  7. Late or On Time Calculation
    By concretetsunami in forum Excel General
    Replies: 7
    Last Post: 03-24-2008, 04:26 PM

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