+ Reply to Thread
Results 1 to 8 of 8

Need Formula to populate a cell +9 hours

  1. #1
    Registered User
    Join Date
    11-03-2011
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    5

    Need Formula to populate a cell +9 hours

    Very new to excel programming and 1st post here.
    The attached workbook has some examples.
    I have inserted the drop-down time lists to choose Actual Start Time & End Time.
    30 minutes have been deducted for the employee's lunch period, to show Total Hours Worked.
    I need a formula to add 9 hours time to cell H13 from the time in cell E13.
    In our company, if an employee works more than 15 hours continuously the employee is entitiled to a 9 hour rest period.

    example:

    Please Login or Register  to view this content.
    If possible, could the formula populate the word NO in the Change Of Start Time cell, if the criteria (15.1)hours was not achieveed in cell G13?
    example

    Please Login or Register  to view this content.
    Every 6 minutes is a point.

    Thank You,

    KDL
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need Formula to populate a cell +9 hours

    Formula in G13:
    =(E13-B13)*24
    ...format cell as General.

    In H13:
    =LOOKUP(G13, {0,15.01}, {"No","Yes"})
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-03-2011
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    5

    Re: Need Formula to populate a cell +9 hours

    Thanks for your reply. If more than 15 hours is achieved, can the Actual Starting Time
    be populated in H13? Which would be 9 hours from the time in E13.

    And the formula you stated for G13 added back the 30 minutes for the employees lunch period. I need it subtracted.

    Thanks
    Last edited by Paul; 11-04-2011 at 04:38 PM. Reason: Removed quote of full post, unnecessary.

  4. #4
    Registered User
    Join Date
    11-03-2011
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    5

    Re: Need Formula to populate a cell +9 hours

    Formula in G13:
    =(E13-B13)*24

    What can I add to this formula to subtract 30 minutes for an employee's lunch period?

  5. #5
    Registered User
    Join Date
    11-03-2011
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    5

    Re: Need Formula to populate a cell +9 hours

    I added -0.5 to =(E13-B13)*24
    That worked out minusing the 30 min lunch period.
    I added a new cell I13. Named: Actual Change Of Start Time.
    What I still need to figure out is the formula for adding 9 hours to I13 from the result in E13, if More Than 15 hours were worked.

    Thank You

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need Formula to populate a cell +9 hours

    wWhat is the formula in I13?

  7. #7
    Registered User
    Join Date
    11-03-2011
    Location
    Philadelphia, Pa
    MS-Off Ver
    Excel 2007 & Excel 2010
    Posts
    5

    Re: Need Formula to populate a cell +9 hours

    Quote Originally Posted by JBeaucaire View Post
    wWhat is the formula in I13?
    That is what I am trying to figure out. I have uploaded the new workbook with new formulas.
    There seems to be a problem with the formula in G13. If I enter 17:00 for Start Time
    and 7:30 AM for End Time, the result in G13 is -10 Hour Worked. Then in H13 produces a result of #N/A.

    2nd Problem:

    I added 2 new cell's Named: Actual Change Of Start Time I13 and Day Off Achieved J13.
    Not sure if its even possible but, can I13 show + 9 hours of time from E13(EndTime) if
    the employee worked more than 15 hours?

    Now for J13 Day Off Achieved ? Here is a scenario:

    Employee starts work at 7:00 AM thru 3:30pm, then works a double shift in a 3:00pm crew
    and works until 3:30 am.

    7:00am to 3:30 (-lunch period) = 8.0 hours
    3:30pm to 3:30 am = 12.0 hours
    20.0 hours worked

    3:30AM +9.0 hours rest period = 12:30pm start which is 5.5 hours into his/her workday.

    If this employee is scheduled to start at 7:00am the next day he/she would be entitled to the day off with pay. More than five (5) hours of the employee's normally scheduled work tour lied within the nine (9) hour rest period.

    So for J13 Day Off Achieved ? I would need YES or NO.



    Thank You
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Need Formula to populate a cell +9 hours

    G13: =((E13-B13)+(E13<B13))*24-0.5

    I13: =IF(G13>15,E13+TIMEVALUE("9:00"), E13)

    J13: (I don't follow that yet....)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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