5 Replies Latest reply: Jan 18, 2013 6:23 AM by homegrowntx
homegrowntx Level 1 Level 1 (0 points)

I have read the numerous other articles on time and just don't get it.  I'm a long time Excel user and new to Numbers.  I can make things work in Excel, but not in Numbers.

 

I need a formula for timekeeping that will take into account that some IN time is one day and the OUT time is on the next.  As you experts would expect, I get negative numbers.

 

I am only concerned with IN and OUT ... no break time in between.

 

I'd like to see IN @ 17:00 and OUT @ 03:15 equals 10:15.

 

The Excel forumla is =((C2-B2)+(B2>C2))*24

 

But that doesn't work in Numbers.

 

Any help would be appreicated!

  • 1. Re: Help with Time calculation
    Badunit Level 6 Level 6 (10,815 points)

    That can work in Numbers except that because you have that binary (true/false statement) in there it is converting it to a decimal result of 10.25 which is 10.25 hrs = 10 hrs 15 min. You can convert it back to a duration (hours and minutes) with a formula but there is more you need to know first. Plus, the use of "B2>C2" to create a 0 or 1 is a simplification that will cause a warning about using a binary as a number.

     

    In Numbers you have to be a bit more careful when using "time" values. "Time" values are really "Date & Time" values.  If you enter a "time" in your spreadsheet today, it will be that time on today's date.  If you enter it tomorrow it will be that time with tomorrow's date. That could be handy if you are using it to clock in and clock out and you are entering time at the moment you clock in or out. In that case you can use the simpler formula = C2-B2.

     

    But if you are making a schedule and you will be entering "time" data on different days and maybe going back tomorrow to edit the schedule, you need to strip the dates before you do any calculations. This is going to be a lot more complicated than the Excel version.

     

    =DURATION(,,(TIMEVALUE(C5)-TIMEVALUE(B5)+IF(TIMEVALUE(B5)>TIMEVALUE(C5),1,0))*24 )

     

    If you want to use the result in a calculation, such as multiplying by an hourly wage, you don't want it to be a duration, you want a decimal value:

     

    =(TIMEVALUE(C5)-TIMEVALUE(B5)+IF(TIMEVALUE(B5)>TIMEVALUE(C5),1,0))*24

  • 2. Re: Help with Time calculation
    Badunit Level 6 Level 6 (10,815 points)

    Forum went down before I could edit my post.  I misread your original formula the first time around and left out a set of parentheses. I didn't notice until I tried doing the same in Excel and got wacky results there. However, in Excel your formula gave me 10.25, not 10:15. maybe some format other than "Number" is required?

     

    This is the equivalent in Numbers:

    =C2-B2+(B2>C2)*24

    or

    =(TIMEVALUE(C2)-TIMEVALUE(B2)+IF(TIMEVALUE(B2)>TIMEVALUE(C2),1,0))*24

     

    The first has the problems I mentioned in my first post.

    The second one is of the same form as the Excel version and gives the same result.

  • 3. Re: Help with Time calculation
    homegrowntx Level 1 Level 1 (0 points)

    I used the Time format to show 10 hours and 15 minutes were worked.  Let me see if I can muddle through using your formula.  I appreicate the prompt reply.

  • 4. Re: Help with Time calculation
    Jerrold Green1 Level 7 Level 7 (28,990 points)

    HG,

     

    Here's an expression that I hope will help with the muddling....

     

    Assume that your Start time is in C2 and your End Time is in C3, then this expression will calculate the time worked in hours and minutes, while acocunting for time worked across the midnight hour:

     

    =IFERROR(DURATION(,,((TIMEVALUE(C3)-TIMEVALUE(C2)) + IF(TIMEVALUE(C3)< TIMEVALUE(C2), 1, 0))*24,,), "")

     

    Jerry

  • 5. Re: Help with Time calculation
    homegrowntx Level 1 Level 1 (0 points)

    Thanks for the help!