Results 1 to 3 of 3

Thread: Time Table is giving me trouble

  1. #1
    Neophyte davsank's Avatar
    Join Date
    Feb 2013
    Location
    A, A
    Posts
    1
    Articles
    0

    Time Table is giving me trouble



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,
    I'm sorry if this thread belongs at another forum, it just seemed as the closest one.

    Anyway, I'm trying to build a not so regular time table (as where I come from breaks and such work differently) so basically, I got columns for dates(A) , day names(B), punch-in hours(C), punch-out hours(D), total work hours(E) and additional hours(F) and a few others. (sorry if I use the wrong terminology, I'm translating from Hebrew).

    The formula I'm using to calculate the work hours is "=IF((D2-C2)<0,D2-C2+24,D2-C2)" so that it can also take into account night shifts or any shift that ends after 24:00.

    the problem come at the very very last row called totals. the total for the work hours should show the work hours for the entire month, so I tired using "=SUM(E2:E31)" however it resets on 24:00 (so if someone worked 25 hours in a month for example, it'll only show 01:00).

    How can I fix this?

    I'm adding an Example File called Example.xlsx for may 2013 as this is the soonest I'm going to start using this - I'm using Excel 2010 if it's relevant.
    Attached Files Attached Files

  2. #2
    Hi davsank

    Format the total cell as [h]:mm. Format cells > Number > Custom > Type: "[h]:mm" (not inc. quotes)

    Kevin

  3. #3
    You can also simplify your formula to

    =MOD(D2-C2,1)

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
  •