+ Reply to Thread
Results 1 to 4 of 4

calculating lates (time)

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2010
    Posts
    11

    calculating lates (time)

    Hi. I'm trying to calculate if the employee is late or just in time. The shift starts 12 am and the employee logged in 23:52. When I deduct it, it's giving me the value 23:52 as LATE when in fact the employee logged in earlier than the shift start. Not sure what to do. Thanks in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    553

    Re: calculating lates (time)

    You need the day as well as the time OR some sort of error checking to correct as it is naturally taking 23:52 is after 12:00 on the same day.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily NL, sometimes UK
    MS-Off Ver
    2010
    Posts
    2,251

    Re: calculating lates (time)

    What you need to know about dates and times in Excel is that they are just numbers. 1.0 is midnight at the start of 1st Jan 1900. 1.5 is midday (12pm) on 1st Jan 1900. 43751.25 is 6am on 13th Oct 2019.
    If you enter 23:52 without any date, Excel interprets that as the number 0.9944, which is greater than 0, which is the default 'midnight' or '12am' so appears as Late.

    There are various ways of fixing this, as torachan says above, but the best one for you will depend on how your data is laid out.

    So, please attach a sample workbook.
    Make sure there is enough data to demonstrate your need but not so much that the file is too big (if you have 10,000 rows of data, you don't need to leave them all in, for example). Include before and after sheets in the workbook if needed to show the process you're trying to complete or automate. Make sure you include the answers you want to get - put examples in, with notes if necessary to explain details.
    Remember to remove any confidential information or, if a cell needs data in it to work, overwrite it with made-up data.
    The paperclip icon doesn't work at the moment, so click on Go Advanced under the reply box and then scroll down to Manage Attachments to attach a file.
    Regards,
    Aardigspook

    My internet connection can be intermittent, so sometimes I may not reply quickly to questions. Sorry - it's not personal and I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, 365
    Posts
    10,763

    Re: calculating lates (time)

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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