+ Reply to Thread
Results 1 to 11 of 11

Identify time elapsed after each hour

  1. #1
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Identify time elapsed after each hour

    Is there a formula that I could use to identify the time elapsed after the hour?

    End result I am trying to achieve is;

    Student A;
    Due in: 09:00:00
    Arrived: 09:06:51
    Result = "Late"

    Student B;
    Due in: 08:00:00
    Arrived: 07:58:13
    Result: "Ok"

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,860

    Re: Identify time elapsed after each hour

    Hi many,

    See if the attached with a simple If statement does what you want.

    Arrive Late.xlsx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Identify time elapsed after each hour

    If all you want is 'OK' or 'Late' then, assuming that row 1 is a header row and that Due in' is in A2 and 'Arrived' in B2, use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you want the elapsed time as well, try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - 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
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Identify time elapsed after each hour

    Quote Originally Posted by Aardigspook View Post
    If all you want is 'OK' or 'Late' then, assuming that row 1 is a header row and that Due in' is in A2 and 'Arrived' in B2, use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    If you want the elapsed time as well, try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I should made it clear, I have attached what I am trying to create; Lateness is anything over 1 minutes

    Timelog:
    This will have log in times dumped in from system report containing username and time logged in

    Track:
    This will have username and the weekdays with Vlookup if username is in timelog array then "In" otherwise "Update" (update meaning check and manually update) -- Ideally, I would like to nest the time formula here and identify lateness here

    Lateness:
    Same template as Track as I wasn't able to nest in the lookup formula for lateness. This would be used for "late" otherwise "ok"

    Each student would arrive at different times during the morning, some 8:00:00, some 09:00:00, some 10:00:00 etc and with just their First Login time available, makes it difficult for me....
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,307

    Re: Identify time elapsed after each hour

    How do we know if they are late if we don't know expected (required) arrival time: this latter time does not appear in your supplied data?

  6. #6
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Identify time elapsed after each hour

    Quote Originally Posted by MarvinP View Post
    Hi many,

    See if the attached with a simple If statement does what you want.

    Attachment 504304
    Sorry, "Due in" was more figurative. I would only have their "Arrive" time - see attatchment

    The only way I can recognize Lateness is anything > 1 minute past the hour. EG- 08:01:58 = Late
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Identify time elapsed after each hour

    Quote Originally Posted by JohnTopley View Post
    How do we know if they are late if we don't know expected (required) arrival time: this latter time does not appear in your supplied data?
    Sorry, "Due in" was more figurative. I would only have their "Arrive" time - see attachment above

    The only way I can recognize Lateness is anything > 1 minute past the hour. EG- 08:01:58 = Late

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,307

    Re: Identify time elapsed after each hour

    So "Adam Wiiliams" is nearly 30 minutes late (not early for 13:30 start?).

  9. #9
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Identify time elapsed after each hour

    Quote Originally Posted by JohnTopley View Post
    So "Adam Wiiliams" is nearly 30 minutes late (not early for 13:30 start?).
    That's Correct! Anything past the hours, over 1 minute is late.

    Actually, To identify early starters, no is able to log in until 15 minutes to the hour EG- 08:45 for 09:00 starters etc, but 08:32 would be late

    It's very tricky with these conditions

  10. #10
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Identify time elapsed after each hour

    Actually, so not to over complicate things, anything past the hour mark more than a minute would be late

  11. #11
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    Re: Identify time elapsed after each hour

    Using this in column C of 'Timelog' will identify if someone is late:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This will give how much he/she is late by:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The limitation of this is the 45 min part - if someone's late by 45 or more minutes, this will assume he/she is on time. Without an expected arrival time, I don't see any way round this.

    I'm not sure exactly what you want on the 'Track' or 'Lateness' sheets.

+ 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] Converting an elapsed time in decimal number format to an actual time :S
    By Spicey_888 in forum Excel General
    Replies: 3
    Last Post: 07-20-2014, 08:53 PM
  2. Replies: 0
    Last Post: 03-07-2014, 11:26 AM
  3. Need help to convert timestamped dates into elapsed days, hour, minutes
    By BBaity in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2013, 01:49 PM
  4. VBA to convert 12 hour to 24 hour time
    By Krrattner in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2012, 06:59 PM
  5. Elapsed Time by Hour of the Day
    By eternalimit in forum Excel General
    Replies: 4
    Last Post: 10-13-2011, 02:28 PM
  6. Using start/end time fields on userform to populate elapsed time on spreadsheet
    By Lothar69 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2011, 05:07 PM
  7. Convert 12 hour to 24 hour time
    By RussellNonBrand in forum Excel General
    Replies: 5
    Last Post: 01-26-2011, 03:01 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