+ Reply to Thread
Results 1 to 4 of 4

Calculating hours worked beyond 12 midnight

  1. #1
    Registered User
    Join Date
    11-04-2018
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    4

    Calculating hours worked beyond 12 midnight

    Hello There experts..Can somebody please help me on this. I am trying to calculate the total hours worked but if it will go beyond 12 midnight, it will not give me the correct time... please help me.. this is my codes..
    my log in time is this 11/26/2018 8:29 and my log-out time is 11/27/2018 15:24



    Dim FIRSTDATE As Date
    Dim SECONDDATE As Date
    Dim N As Long
    Dim I As Integer
    Dim LASTROW As Long
    Dim WS As Worksheet

    Set WS = Worksheets("DateTime")
    LASTROW = WS.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To LASTROW
    If WS.Cells(I, 5) <> "" And WS.Cells(I, 6) <> "" Then
    FIRSTDATE = TimeValue(WS.Cells(I, 5))
    SECONDDATE = TimeValue(WS.Cells(I, 6))
    N = DateDiff("S", FIRSTDATE, SECONDDATE)
    WS.Cells(I, 7) = N
    WS.Cells(I, 7) = Cells(I, 7) / 3600
    WS.Cells(I, 7) = Round(Cells(I, 7))
    UserForm4.TextBox1.Value = WS.Cells(I, 7)
    End If
    Next I

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculating hours worked beyond 12 midnight

    Hi,

    It helps if you always upload the workbook rather than expecting us to imagine from the code what the layout of the sheet is. Please note for the future.

    After the line

    Please Login or Register  to view this content.
    Add
    Please Login or Register  to view this content.
    That said why complicate the process with VBA which should always be a last resort. Don;t forget yuo can always use Data Validation cells for user inputs.

    Why not just use in G2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you need the result for a userform then just read the value from the worksheet.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    11-04-2018
    Location
    Philippines
    MS-Off Ver
    2016
    Posts
    4

    Re: Calculating hours worked beyond 12 midnight

    Thank you so much #Richard Buttery i really do appreciate your help..it's a big relief thank you thank you..

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Calculating hours worked beyond 12 midnight

    Quote Originally Posted by tyneexi View Post
    Thank you so much #Richard Buttery i really do appreciate your help..it's a big relief thank you thank you..
    My pleasure.

+ 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. Replies: 8
    Last Post: 09-30-2017, 07:00 PM
  2. Replies: 5
    Last Post: 04-13-2017, 11:05 AM
  3. [SOLVED] Calculating hours worked even past midnight
    By tph1956 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-30-2013, 12:52 PM
  4. [SOLVED] Calculating hours worked beyond midnight
    By Boville in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-25-2012, 12:22 PM
  5. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  6. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 AM
  7. Replies: 0
    Last Post: 01-05-2012, 06:23 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