To automate Calculation of time taken

Suggest you post up a copy of your worksheet as you have cells listed in the second formula for which you have not provided values. Difficult to analyze what the patient is suffering from if you cannot see him.

The formula I have used is

=IF(TEXT(A1, "hh:mm")>"18:00",0,TEXT((DATE(YEAR(A1),MONTH(A1),DAY(A1))+"18:00")-A1,"hh:mm"))

When working with dates and times, don't convert back and forth to text. Here is a simpler formula that will give you the same result, and the result will be in time format instead of text.

Formula:

=MAX(0,INT(A1)+TIME(18,,)-A1)

For the second time i calculate the time taken from 9:30 in the morning which is

=IF(DATE(DAY(B1),MONTH(A1),YEAR(A1))=DATE(DAY(H10),MONTH(H10),YEAR(H10)),0,TEXT(B1-(DATE(YEAR(B1),MONTH(B1),DAY(B1))+"9:30"),"hh:mm"))

What is in B1? H10? If you are trying to calculate the time from 9:30 AM to 5:05:00 PM (and return 0 if it is earlier than 9:30), the similarly you can use

Formula:

=MAX(0,A2-INT(A2)-TIME(9,30,))

Then you can easily add these two results to get a valid time.