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

                                         Google Spreadsheet   Post  #1604
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-15-2014
post by Amos Arnold: (https://productforums.google.com/forum/#!mydiscussions/docs/54r7CoHcpxM)
Find earliest time in an array but don't display 0
My Sheet is here: https://docs.google.com/a/emethgym.com/spreadsheets/d/1WajUulK0CQH1vbNv-IJ9ilm1Fe1PMARgZg7rTi9zxoQ/edit#gid=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