+ Reply to Thread
Results 1 to 3 of 3

Time Accounting Problem

  1. #1
    Registered User
    Join Date
    10-06-2007
    Posts
    15

    Time Accounting Problem

    I need some help with a formula that will count half days so that I can track expenses for a nanny. I will pay the nanny $40 for each half day that she works. A half day is defined as 1 minute up to 12 hours. At 12 hours and 1 minute it starts a new half day and another $40.

    So if the nanny worked 2 days and 6 hours, the cell would show 2.5 days (or 5 half days). Then I can multiply 5 half days times $40 for the total.

    I know how to show the time at it is displayed as hh:mm dd/mm/yy.
    I also know how to add the start and end cells up to show how many days it is. It usually shows something like 3.2365 days as an example. I would want that to display 3.5

    Once I get the correct formula, I can multiply that result with the rate which is $40 a half day for the total.

    A1 is (Start Time)
    B1 is (End Time)
    C1 is (B1-A1) rounded to the next .5 or round number) 6, 6.5, 7,7.5 etc...


    Once I can get cell C1 showing the correct half days, I can finish the rest.

    Thanks.

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    For C1 try

    =CEILING(B1-A1,0.5)

  3. #3
    Registered User
    Join Date
    10-06-2007
    Posts
    15
    Thanks so much. That did the trick. I did not know about the Ceiling function. I also looked up the Floor function and that will help as well.

    I had a sheet previously built that added whole days and slotted the total days into each individual month and then added a total. By using your Ceiling and Floor functions, I will be able to use a Start time, End Time, and have it figure out all the Half days and slot the totals into each month. I got it working so now I need to build the entire sheet and make it look pretty.

    Thanks. You saved me many headaches.

+ 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