Time value that should be 24:00 displays as 0:00

Format your C8 and C9 as Custom format [h]:mm

Then, put on C9 this formula =1-C8 instead of =24-C8

Putting 24 on your C9 will break your formula if there is a value on E48

What you have now is working for the wrong reason.  Obviously, E48 is a number of hours, so C8, which contains =E48/24, is a number of days.  If E48 is between 0 and 24, then C8 will be between 0 and 1, expressing a fraction of a day.  This corresponds to the way Excel’s date/time storage works — a date/time is stored as the number of days (including a fraction of a day, as necessary) since midnight, January 1, 1900.  That’s why C8 is correctly displaying as the number of hours and minutes corresponding to the number of hours in E48.

But, if E48 is between 0 and 24, and C8 is between 0 and 1, then 24-C8 is a number between 23.0 and 24.0.  Specifically, if (as in your example) E48 is 15.5, then C8 is 0.645833333 and C9 is 23.35416667.  Since C8 expresses a number of days, the 24 is interpreted as a number of days.  If you change the format of C8 and C9 to a custom format of d "days and" h:mm, you will see that, for E48 = 15.5, C8 will be 0 days and 15:30 and C9 will be 23 days and 8:30.

So, two things:

• Since C8 is a number of days, you want C9 to be =1-C8 — computing the rest of the day, and not looking at a 24 day period. 

• In an Excel cell display format for a date/time value, the h (and hh), mm, ss, and AM/PM tokens look only at the fractional part of the value — i.e., the fraction of a day, which corresponds to a time of day.  (Note the way the 23.35416667 value is displayed as 23 days and 8:30; the 8:30 corresponds to the 0.35416667 fraction.)

  A blank E48, of course, is interpreted as a value of 0, and will cause C8 to evaluate to 0.  With C9 set to =1-C8, it will evaluate to 1.  If you still have C9 formatted as d "days and" h:mm, you will see that it displays as 1 days and 0:00, which, of course, is equivalent to 24 hours.  To get it to display as 24:00, you need to use [h] in the format instead of just plain h; i.e., use a format of [h]:mm instead of just plain h:mm.  The [h] token looks at the entire value in the cell (not just the fractional part) and will display hours ≥24.