Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Excel Time Calculation (Excel XP)

    Have created a simple time sheet calculator that allows a user to enter a start and finish time in the form hours:minutes and which then calculates the difference between these two times and presents the result as a decimal time (i.e. 5:30 is 5.5). This works very well, but I'm looking to be able to catch the time input for start and finish and round it up or down to the nearest quarter of an hour. For example, if the user enters 5:10, the time should be rounded up to 5:15. Similarly if they had entered 5:07, the time would be rounded down to 5:00. (I would want to round up based on 8 minutes past a particular quarter of an hour, and not bother with the exact 7min 30 second split of a quarter of an hour).
    Can anyone suggest a neat way to accomplish this as so far I've not been able to come up with an answer based on the limited programming knowledge I have of Excel. Grateful for any tips!

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Calculation (Excel XP)

    This can be done by using VBA worksheet_change event.

    Start the Visual Basic Editor (Alt+F11)
    Display the Project Explorer (Ctrl+R) - may already be displayed
    Display workbook in the tree view mode - may already be in the tree view (list of all the worksheets)
    Double click the worksheet where the user inputs the times
    Type or copy the following code. Replace "A1:A10" with the range that contains the inputted times
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Dim rCell As Range
    For Each rCell In Intersect(Target, Range("A1:A10"))
    If Not rCell.HasFormula Then _
    rCell = Application.WorksheetFunction.Round(rCell * 96, 0) / 96
    Next
    Set rCell = Nothing
    End If
    End Sub</pre>


  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Time Calculation (Excel XP)

    If you are using times then a formula like this should work
    <pre>=MROUND(A1,0.25/24)</pre>

    and format as desired time. (excel times are all in days and there are 24 hrs /day)

    If you have decimal times (they already are multiplied by 24 to get you the hours) and you could use something like:
    <pre>=MROUND(A1,0.25)</pre>

    these should not be formatted as times

    MROUND is in the analysis toolpack.

    If you don't want to install it or plan on sharing with people who migh not have it installed you can use:
    <pre>=INT(A1*24/0.25+0.5)*0.25/24</pre>

    or
    <pre>=INT(A1/0.25+0.5)*0.25</pre>


    Steve

  4. #4
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Calculation (Excel XP)

    Thanks to both Steve and Tony here for some good tips! I'll give them a try and let you know how I fared!
    Cheers,
    David

  5. #5
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Calculation (Excel XP)

    Hi Tony,

    Thanks for this excellent solution, which does exactly what I wanted to do for the in - out times in rounding them up or down. However, including the VB script has brought a problem with it. I have included a simple recorded macro in my time sheet which selects the range of in - out times (three shifts per day for a seven day week) and deletes them so that a new series of times can be entered. Leaving all of the in - out time cells blank allows the time sheet to have entries for only two or three days per week and one, two or three shifts as needed. The remainder of the input cells remaining blank. The sum of the times is then calculated for just the days entered.
    When the VB script you created runs, it rounds the in - out times nicely. However, when I clear the cells using my macro, the cells re-populate with 00:00 resulting in each input cell having a value - which means that the sheet is not easily used for only one or two days and for the number of shifts actually worked. (As it tries to add all these times up, resulting of course in a wrong answer)!
    Is there a way to modify your script to make it enter a blank (no data) into the cells when I clear them? Would be very grateful for a solution to do this. Many thanks for your help so far.
    Cheers, David

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Time Calculation (Excel XP)

    Try this modification:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    Dim rCell As Range
    For Each rCell In Intersect(Target, Range("A1:A10"))
    If (Not rCell.HasFormula) And (rCell.Value <> "") Then _
    rCell = Application.WorksheetFunction.Round(rCell * 96, 0) / 96
    Next
    Set rCell = Nothing
    End If
    End Sub
    </pre>

    Legare Coleman

  7. #7
    Lounger
    Join Date
    Jun 2003
    Location
    Singapore, Singapore
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Time Calculation (Excel XP)

    Hi Legare,
    Thanks for the swift and completely on target response to my question. Does the trick nicely! Really appreciate the help!
    Cheers,
    David

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •