Tuesday, April 15, 2014

yogi_Pull The Earliest Time The Latest Time For Making Specified Adjustments And Total Time For Weekday AM And PM Shifts

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-15-2014
Find earliest time in an array but don't display 0

I am trying to create a schedule sheet based upon data in another sheet.
• Data in Rows 18-30+ is there by =Query formula
• In F4:G15 I would like to pull information from lower "Scheduled coaching:.." area:
• specifically, I am trying to pull the earliest start time per each day- separately for am or pm shifts.
• further I am trying to show the earliest time as less 15 mins, and the latest time as plus 15 mins.
• The above items I have achieved in the existing spreadsheet, my problem is that it is also giving me results for days in which there are no shifts/classes.
• Example: F7 turns up 12AM-15 mins, or 11:45PM. I need F7 to return a blank or "--" so that it does not create a 30 mins shift out of nothing
---
I can describe more fully:

In cell F7 I currently have this formula: =SUM(MIN(ArrayFormula(IF(\$F\$19:\$F=E7,\$H\$19:\$H,""))),-"00:15:00")
• This formula is returning value of 0? formatted as time = 12:00AM, which then turns into 11:45PM because of the SUM part of the formula.
• I would like F7 to return nothing, or "--" so that H7 equals 0.
-----------------------------------------------------------------------------------------------------------------------------------------------
following is a solution to a bit more generalized problem