Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Melbourne, Australia
    Posts
    22
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Problem with formula

    I have a spreadsheet to calculate the cost of paying someone per day, based on the start and end times.
    I have custom time fields in columns B,C and D with format h:mm Column B contains the start time in 24 hour format, column C contains the finish time and column D contains the difference, with the formula =IF(C2="","",C2-B2) for row 2 and the equivalent for subsequent rows.
    Column E contains the amount earned with the formula =IF(C2="","",(HOUR(D2)+MINUTE(D2-TIME(HOUR(D2),0,0))/60)*22) which takes the whole hours +the minutes/60 (i.e., fraction of an hour) and multiplies the result by 22 to give the amount earned.

    This works correctly in most cases, but in certain cases where the time worked is an exact number of hours column E (the earned amount) gets a #NUM! error. Changing both of the times by only 2 minute results in the correct result.
    Examples 9:00 16:00 gives the correct value in D (7:00) but column E gets the error
    9:01 16:01 gives the correct value in D (7:00) but column E gets the error
    9:02 16:02 gives the correct values in D and E
    but 16:00 and 21:00 gives the correct values in D and E.

    I have tried using the standard time format for the three columns and also the custom hh:mm, with no effect.

    Has anyone any idea of what is wrong?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,170
    Thanks
    47
    Thanked 980 Times in 910 Posts
    I always let Excel do the time calculation, which means entering the time in the format hh:mm. Then your formula would be:
    =IF(C2="","",(D2*24*22))

    Excel stores the date/time data as a number, with 1 being 24 hours, so you multiply by 24, then by your rate.

    cheers, Paul

Tags for this Thread

Posting Permissions

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