Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Subtracting 30 minutes from time in and out for schedule

  1. #1
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Subtracting 30 minutes from time in and out for schedule

    I am attempting to figure out how to change the formula to automatically subtract a 30 minute lunch if the total time for the day exceeds 6 hours worked.

    B value Time of day punch in: 9:30 AM
    C value =IF(D11="","","-")
    D value Time of Day punch out: 6:00 PM
    E value =IF(D11="","",IF(D11<=B11,((VLOOKUP(D11,TValue,3,))-(VLOOKUP(B11,TValue,2,))),((VLOOKUP(D11,TValue,2,))-(VLOOKUP(B11,TValue,2,)))))

    E, is of course indicating the total time for the day of 8.50, vs the actual hours worked minus a 30 minute lunch.


    This is a spreadsheet that I am attempting to change to help out my manager keep track of total employee weekly hours better.

    Please keep it simple. I am not versed at all in VBA.


    Any assistance on this would be greatly appreciated,

    Thanks,

    Snedman

  2. #2
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting 30 minutes from time in and out for schedule

    Hi snedman,

    Does this help?...


    Sheet1

     ABCDEFGHIJ
    1NameStart Time End TimePaid Hours HoursLunch Hours Worked
    2Bill9:30 AM 6:00 PM8:00 06:0000:30 8:30
    3Bob11:30 AM 5:00 PM5:30    5:30
    4Tom9:00 AM 5:30 PM8:00    8:30
    5Tim6:00 AM 5:00 PM10:30    11:00

    Spreadsheet Formulas
    CellFormula
    E2=IF(D2-B2>$G$2,D2-B2-$H$2,D2-B2)
    J2=D2-B2


    Excel tables to the web >> Excel Jeanie HTML 4


    The formula in E2 needs to be copied down.

    Good luck.

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  3. #3
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting 30 minutes from time in and out for schedule

    I am having a hard time posting in a screenshot/html of the actual spreadhseet.

    Any advice.

    The links I followed for the HTMLMaker I can't get to work correctly for taking a range of the spreadhseet to post.
    Last edited by snedman; May 26th, 2011 at 03:22 PM.

  4. #4
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting 30 minutes from time in and out for schedule

    Akashwani, there is not actual place in the spread sheet for a lunch time. It is assumed that if you work an 8 hour shift a lunch is taken.

    The 30 minutes would need to be deducted from the time autmatically from time in and time out only.

  5. #5
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting 30 minutes from time in and out for schedule

    I will make yours work for me.

    Thanks for the help.

  6. #6
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting 30 minutes from time in and out for schedule

    Hi,

    Does this work?.....

    =IF(D2-B2>$G$2,D2-B2-"00:30",D2-B2)

    And if you cannot put a time value in G2.....

    =IF(D2-B2>"06:00",D2-B2-"00:30",D2-B2)

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  7. #7
    Board Regular Akashwani's Avatar
    Join Date
    Mar 2009
    Posts
    2,900
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting 30 minutes from time in and out for schedule

    Hi snedman,

    That will not work, but this will....

    =IF(MOD(D2-B2,1)>0.25,(D2-B2)-0.5/24,D2-B2)

    Sorry about that.

    You may want to take a look here....

    http://www.youtube.com/watch?v=CtrFnGWqoGE

    Ak
    A reply is not only helpful to others, but polite to those who have provided a solution!!

    To post sample data go here....
    HtmlMaker
    Excel Genie
    Use Borders

    Upload a Sample File


    Visit here for some EXCELlent videos...

  8. #8
    New Member
    Join Date
    May 2002
    Location
    Baton Rouge
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subtracting 30 minutes from time in and out for schedule

    Akashwani,

    That worked beautifully.

    You have my greatest gratitude and appreciation for taking time to help me with this.

    You sir, are a king : ]

    Snedman

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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