+ Reply to Thread
Results 1 to 14 of 14

Computing Hours and Minutes using excel 2010

  1. #1
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Computing Hours and Minutes using excel 2010

    Hi Everyone, I have attached an .xlsx file containing the time ins/out including the lunch breaks. Would someone be kind enough to help me out in producing the desired results as what I have described in the said attachment? Please.. This is a timesheet that I would need to accomplishTimeSheet.xlsx


    Thanks in Advance.

  2. #2
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    awww..66 views and not a single reply..

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Computing Hours and Minutes using excel 2010

    so your "in" time has to be 08:00am or later and your "out" time has to be 05:00pm or earlier?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Computing Hours and Minutes using excel 2010

    HTH
    Regards, Jeff

  5. #5
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    please check the attachment as I have mentioned the conditions there.. "in" has to be 8 or earlier out should be 5PM or later. thanks!

  6. #6
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    Thanks for the reply. It does the correct computation. However if you are to check on my attachment you will see the conditions that I have included and the desired output on my time sheet. Please check it. Thanks again.

  7. #7
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    Quote Originally Posted by FDibbins View Post
    so your "in" time has to be 08:00am or later and your "out" time has to be 05:00pm or earlier?
    please check the attachment as I have mentioned the conditions there.. "in" has to be 8 or earlier out should be 5PM or later. thanks!

  8. #8
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    Quote Originally Posted by jeffreybrown View Post
    Thanks for the reply. It does the correct computation. However if you are to check on my attachment you will see the conditions that I have included and the desired output on my time sheet. Please check it. Thanks again.

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Computing Hours and Minutes using excel 2010

    Not really sure what you find wrong with the link proposed, but this get's the results in column E

    E2 and drag down
    =(B2<A2)+B2-A2+(D2<C2)+D2-C2

  10. #10
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    Quote Originally Posted by jeffreybrown View Post
    Not really sure what you find wrong with the link proposed, but this get's the results in column E

    E2 and drag down
    =(B2<A2)+B2-A2+(D2<C2)+D2-C2
    Please check this TimeSheet file: TimeSheet.xlsx

  11. #11
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    Quote Originally Posted by kraddark View Post
    Please check this TimeSheet file: Attachment 155387
    Also if you are going to drag everything down from E2, you will notice that "SOME" of the results are different from the desired results. Kindly, check the notes for me.
    Thanks a lot!

  12. #12
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Computing Hours and Minutes using excel 2010

    Jeff's formula is completely valid. You just need to standardize your times first. Look at the attachment. I have added four new helper columns for standardization to occur. Also you made a mistake in Row 12. The correct result is in the attachment.

    Good luck.

    abousetta
    Attached Files Attached Files
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  13. #13
    Registered User
    Join Date
    05-22-2011
    Location
    Philippines
    MS-Off Ver
    Microsoft Office LTSC Professional Plus 2021
    Posts
    55

    Re: Computing Hours and Minutes using excel 2010

    Quote Originally Posted by abousetta View Post
    Jeff's formula is completely valid. You just need to standardize your times first. Look at the attachment. I have added four new helper columns for standardization to occur. Also you made a mistake in Row 12. The correct result is in the attachment.

    Good luck.

    abousetta
    Wow! This is exactly what I've been looking for.. Thanks a lot!

  14. #14
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,421

    Re: Computing Hours and Minutes using excel 2010

    if you could change all of your entered hours to 24-hour format, (08:00 for 8 AM, 17:00 for 5 PM), then you could use the formula, given below, starting in Row 2 (of any column where you don't have data) and drag-fill down.

    it is as simple as selecting all the cell where you have entered your time in AM/PM format and applying the format [h]:mm; excel will automatically update 8 AM to 08:00 and 5 PM to 17:00 etc.

    Please Login or Register  to view this content.
    Last edited by icestationzbra; 05-12-2012 at 02:43 AM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1