timet1 = Format$(Now, "h:mm:ss")
That line sets timet1 to a string, later, in the hightlighted line, the subtraction causes a type mismatch.

Try this:
Sub Expected_Update_Data() 
     
    Dim timet1 As Double, timet2 As Double
     
    timet1 = Now
     
    Call Update_Expected(Range("S1")) ' For Jun07
    Sheets("Expected").Activate 
     
    ActiveSheet.Range("A1").Select ' Finish off at EXPECTED cell A1 once macro runs completely
     
    ActiveSheet.Range("B1").Value = "EXPECTED macro completed at " & Format$(Now, "dd/mm/yyyy h:mmam/pm") 
     
    timet2 = Now
     
     ActiveSheet.Range("B3").Value = "The entire process took " & Format$(timet2 - timet1, "h:mm:ss") & " hours." 
     
End Sub
How long do you expect this routine to take?