Need Formula to populate a cell +9 hours

[KDL]

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:

 B13                  E13                    G13                       H13
Actual Start Time    End Time      Total Hours Worked       Change Of Start Time
       7:00 AM          23:30                   16.0                  8:30 AM

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

  B13                   E13                    G13                              H13
Actual Start Time    End Time      Total Hours Worked       Change Of Start Time
        7:00 AM           22:30                    15.0                NO

Every 6 minutes is a point.

Thank You,

KDL

[JBeaucaire]

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

In H13:
=LOOKUP(G13, {0,15.01}, {"No","Yes"})

[KDL]

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

[KDL]

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

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

[KDL]

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

[JBeaucaire]

wWhat is the formula in I13?

[KDL]

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

[JBeaucaire]

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

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

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