+ Reply to Thread
Results 1 to 6 of 6

Compute For Total Hours/Minutes Rendered with special conditions

  1. #1
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    54

    Question Compute For Total Hours/Minutes Rendered with special conditions

    Hi,

    I want to generate report that will compute Time Difference with special conditions:

    Schedule of 7:00AM - 4:00PM (code 1) - can be late for up to 7:30 and out of 4:30 anything above 7:30 is considered late even if out is 4:30PM onwards.
    Schedule of 7:00AM - 4:00PM (code 2) - can be late for up to 8:30 and out of 5:30 anything above 8:30 is considered late even if out is 5:30PM onwards.

    I have attached sample sheet. T


    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Compute For Total Hours/Minutes Rendered with special conditions

    Something like this? In G3 and pull down for example.
    Please Login or Register  to view this content.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  3. #3
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    54

    Re: Compute For Total Hours/Minutes Rendered with special conditions

    Quote Originally Posted by ben_hensel View Post
    Something like this? In G3 and pull down for example.
    Please Login or Register  to view this content.
    On G2 and G6 the employee should be marked as late 7 / 59 is 7 hours and 59 mins. so 1 minute late. The output that I am looking for is on columns E and F. I wanted the report to automate that. Thank you.

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

    Re: Compute For Total Hours/Minutes Rendered with special conditions

    I am keeping some calculations broken down into smaller steps to avoid complex formulas that may be hard to understand and/or modify in the future.
    Column H displays the duration using: =D3-IF(B3=1, MAX(C3,TIME(7,0,0)),MAX(C3,TIME(8,0,0)))-1/24
    Column I displays hours using: =INT(H3*24)
    Column J displays minutes using: =MINUTE(H3)
    Column E displays hrs / min using: =I3&IF(J3>0," / "&J3,"")
    Column F displays OT using: =MAX(0,I3-8)&IF(OR(I3<8,J3=0),""," / "&J3)
    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.

  5. #5
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    54

    Re: Compute For Total Hours/Minutes Rendered with special conditions

    Quote Originally Posted by JeteMc View Post
    I am keeping some calculations broken down into smaller steps to avoid complex formulas that may be hard to understand and/or modify in the future.
    Column H displays the duration using: =D3-IF(B3=1, MAX(C3,TIME(7,0,0)),MAX(C3,TIME(8,0,0)))-1/24
    Column I displays hours using: =INT(H3*24)
    Column J displays minutes using: =MINUTE(H3)
    Column E displays hrs / min using: =I3&IF(J3>0," / "&J3,"")
    Column F displays OT using: =MAX(0,I3-8)&IF(OR(I3<8,J3=0),""," / "&J3)
    Let us know if you have any questions.
    This s exactly what I need. I will let you know if there will be additional conditions. Thanks for your assistance.

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

    Re: Compute For Total Hours/Minutes Rendered with special conditions

    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. Compute hours and minutes
    By gcotterl in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-14-2017, 04:38 PM
  2. [SOLVED] Compute total number of hours
    By puuts in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2014, 05:53 AM
  3. [SOLVED] Decimal Time to Hours and Minutes over 24 hours in total
    By FlyingTiger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2012, 06:18 AM
  4. Replies: 8
    Last Post: 01-07-2006, 03:35 PM
  5. Sum minutes and seconds to total hours
    By deck4 in forum Excel General
    Replies: 3
    Last Post: 08-29-2005, 10:05 AM
  6. [SOLVED] sum total hours and minutes
    By Carsonw8 in forum Excel General
    Replies: 2
    Last Post: 08-10-2005, 01:05 PM
  7. [SOLVED] add hours & minutes to other hours & minutes to receive total hou.
    By Wes in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-01-2005, 08: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