+ Reply to Thread
Results 1 to 7 of 7

TimeSheet: When an end time is after Midnight

  1. #1
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    TimeSheet: When an end time is after Midnight

    The below code processes times entered into a TimeSheet.
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    It does the job, except if the END TIME is Less than(next day: After 12:00AM), in which case i get a Negative hours number in the
    Please Login or Register  to view this content.
    I previously tried creating this TimeSheet with Formula's but it got too messy.
    I then used something like:
    =IF(A9<A8,(A9-A8)+(A8>A9)*24
    And got the correct result when the end time was in the next day.

    So i used the same theory for VBA, but it does not work when i tried:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    How can i solve this?

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You don't need VBA to create a Time Sheet, see Chip pearson

    http://cpearson.com/excel/overtime.htm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494
    I think when i was trying the TimeSheet in Fomulas instead of VBA i got the origianl formaul from that site.
    If i use that theory i get:
    Please Login or Register  to view this content.
    But if the Hour Difference is 12hrs, i get a -36hrs as a result??

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You are using VBA and as I said there's no need to. Formulas are fine.

    Attach your workbook

  5. #5
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494
    If it works for a Formula, why does it not work with VBA?

    Is there a way to use a formula in the VBA, as i have too much work to go back to formulas in the project now ?

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You can perform calculations in VBA, but I can't tell what you are doing, that's why I said attach a workbook.

  7. #7
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494
    I think i solved it.
    You steered me in the right direction with Formulas.

    I am getting a result by using both VBA and Formula's with the below:

    Please Login or Register  to view this content.
    Using VBA to place the time values into a sheet, then using a Formula in the sheet to calculate the time differences and then use VBA again to input the sheet's calculated value back to the userform.

    The Formula ended up looking like:
    =IF(E40<D40,(E40-D40+(D40>E40))*24,IF(E40>D40,(E40-D40)*24,IF(AND(E40<>"",D40<>"",E40=D40),24,"")))
    Long winded approach but it looks good thus far.
    Thanks for the assistance.

    Corey....
    Last edited by coreytroy; 07-28-2008 at 05:22 AM.

+ 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