+ Reply to Thread
Results 1 to 11 of 11

Calculate Late and Under time

  1. #1
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Red face Calculate Late and Under time

    Hello and Good Morning everyone,
    im a new user and still new in excel.

    please help me, i seek your guidance,

    A1= time in, 8:30
    B1= time out, 20:00

    office hour start at 08:30am with 1/2hour Grace period,
    but need to cover back late hour within same day after 5.30pm.
    for example;-
    if late 1min need coverback 1min,
    if 15min need coverback 15min

    End hour at 05:30pm, means 8working and 1hour breaktime (12pm to 1pm, no need punch),
    and overtime until time out.

    my excel now, 100% same like my references.
    Please Login or Register  to view this content.
    Can you help me how to create the correct formula?

    your cooperation is highly appreciated.
    Thank you.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Calculate Late and Under time

    Attach a sample workbook here. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Quang PT

  3. #3
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate Late and Under time

    Dear Sir,
    herewith the attached file as a references.
    Thank you for help sir.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate Late and Under time

    Dear Sir, i attached my excel file already, thank you for teaching me how to attached file. Really appreciated. Thank you.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Calculate Late and Under time

    1/- What is the limitation of time IN and OUT?
    For example:
    IN: 1PM and OUT 4PM? (IN after lunch)
    or IN 8AM and OUT 11AM? (OUT before LUNCH)

    2/- Where to define employee is Office Admin or not? cell B3?

  6. #6
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate Late and Under time

    Dear Sir,
    Sorry for less information.

    1/- What is the limitation of time IN and OUT?
    For example:
    IN: 1PM and OUT 4PM? (IN after lunch)
    or IN 8AM and OUT 11AM? (OUT before LUNCH)

    lunch time within 12.00pm to 1pm
    IN = 8.33am, OUT = 12.00pm, IN = 1.00pm, OUT = 8:27pm


    2/- Where to define employee is Office Admin or not? cell B3?

    Yes sir, B3 for Department which is Admin Office or Engineer/Supervisor
    Attached Files Attached Files

  7. #7
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Calculate Late and Under time

    I mean, what if employee came too late? 10AM, or even 2PM?
    Similar, leave too early? 11AM?

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Calculate Late and Under time

    I defined S2:S12 and T2:T12 to store yearly holidays and irregular Offdays
    Assuming that employee come late and leave late is still counted to be overtime ( i.e 11AM ->11PM)
    Name: OVTwd for workdays
    OVTsat for Saturday
    OVThld for Offday and holiday.
    Try file attached.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Post Re: Calculate Late and Under time

    Dear Sir, a big thank you for help me, and sorry for not respond your help a few days ago.

    Sir, column for offday and sunday really work perfectly, thank you again.

    I defined S2:S12 and T2:T12 to store yearly holidays and irregular Offdays
    Assuming that employee come late and leave late is still counted to be overtime ( i.e 11AM ->11PM)

    for this one, i already solve with adding 3more column which is final overtime, late time not coverback, and deduction. so i need manually count & fill late time not coverback and deduction. for final overtime will deduct with deduction column, so my time value will be correct.

    For Saturday Overtime have a few problem. I already write on my excel if sir cant understand what i trying to say, coz im not really good in english.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate Late and Under time

    Our company rules ;-
    08:30 - 09:00 (need cover back until 18:00)
    09:01 - 09:30 (deduct 1 hr working hour)
    09:31 - 12:00 (deduct half day working hour)
    12:30 and more (deduct 1 day working hour)

  11. #11
    Registered User
    Join Date
    11-24-2017
    Location
    Malaysia
    MS-Off Ver
    2016
    Posts
    10

    Re: Calculate Late and Under time

    for your references.
    Attached Images Attached Images

+ 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. Calculate DIFOT - %On Time & % Late
    By Pitstock in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-02-2021, 06:49 PM
  2. [SOLVED] Calculate Late and Under time
    By blues_oo7 in forum Excel General
    Replies: 18
    Last Post: 10-07-2019, 07:49 PM
  3. Replies: 3
    Last Post: 08-12-2017, 01:48 AM
  4. [SOLVED] IF function: Early/On Time/Late Time vs. set window of time
    By hclark579 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-21-2014, 05:37 PM
  5. 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
  6. [SOLVED] Calculate late penality
    By jonas245 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 02-11-2013, 07:33 PM
  7. how to calculate the late and under time
    By gerard_gonzales33 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-12-2012, 02:43 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