Super kanin
Level 1 (8 points)
iOS Apps

Q: Help!!! Caculate working hours.

Dear friends

I had a problerm with caculating.

THis is only part so i can select hand by hand.

but there are so many people and different months,

what shou i do?

 

THank you very much.image.png

iPhone 5s, iOS 9.3.2

Posted on Jun 29, 2016 5:42 AM

Close
Super kanin

Q: Help!!! Caculate working hours.

  • All replies
  • Helpful answers

  • by Barry,

    Barry Barry Jun 29, 2016 11:32 AM in response to Super kanin
    Level 7 (32,271 points)
    Jun 29, 2016 11:32 AM in response to Super kanin

    Hi Kanin,

     

    The error is that you are attempting to add two durations and a number. You need to ensure that the value in each of the cells to be summed is a Duration. The revised formula will do that.

     

    Existing formula: =C2-B2

    Replace with:     =IF(OR(LEN(B)<1,LEN(C)<1),DURATION(,,,0),C-B)

     

    The formula says: If cell B or cell C is empty, the result is 0m (zero minutes), otherwise, the result is C-B.

     

    Note: Numbers is 'smart' enough to recognize when 'B' means 'all of column B' and when 'B' means 'the single cell in this row of column B', which is why I've left the 2 off of each of the four cell references in this formula.

     

    Regards,

    Barry

  • by Super kanin,

    Super kanin Super kanin Jun 29, 2016 1:15 PM in response to Barry
    Level 1 (8 points)
    iOS Apps
    Jun 29, 2016 1:15 PM in response to Barry

    Hi Barry

     

    Thank you for your answer,

    you means in cell D2,D3,D7,D8,D9. I Can use the same formula.

    it seems the Best smart answer, but.....

     

    image.png

     

    D2 worked before, and D9 still does not work...

  • by Barry,

    Barry Barry Jun 30, 2016 1:17 PM in response to Super kanin
    Level 7 (32,271 points)
    Jun 30, 2016 1:17 PM in response to Super kanin

    Hi Kanin,

     

    D2 worked before because you had Date/Time values in B2 and C2. It would also have worked when B2 and C2 were empty, but would have produced a result of 0 (a number)

     

    What result do you see in D9? If it is an error triangle, what is the error message? (click once on the triangle to show the message)

    I notice what's displayed in D9 does not start with an = sign. Did you enter one before entering the formula?

     

    Your formulas are being displayed as text. Displaying them with the formatting applied in the Formula Editor is more useful, as the display shows whether Numbers is recognizing all parts of the formula. When you double click on a cell containing a formula, the formula is displayed in the formula Editor, and should look like this:

    Screen Shot 2016-06-30 at 1.11.12 PM.png

    Note that this display also shows the result produced by the formula, which can also be useful information.

     

    Regards,

    Barry