Skip navigation

Help with Time calculation

331 Views 5 Replies Latest reply: Jan 18, 2013 6:23 AM by homegrowntx RSS
homegrowntx Calculating status...
Currently Being Moderated
Jan 17, 2013 12:43 PM

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!

  • Badunit Level 6 Level 6 (10,765 points)
    Currently Being Moderated
    Jan 17, 2013 1:48 PM (in response to homegrowntx)

    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

  • Badunit Level 6 Level 6 (10,765 points)
    Currently Being Moderated
    Jan 17, 2013 2:09 PM (in response to homegrowntx)

    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.

  • Jerrold Green1 Level 7 Level 7 (28,195 points)
    Currently Being Moderated
    Jan 17, 2013 3:04 PM (in response to homegrowntx)

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.