Formula not displaying value. Instead it displays #N/A

I have a formula that takes the date and time from another worksheet and gets only the date. The problem I have is that after I know it works and I close and re-open it the date from the formula says #N/A. I am unsure of why. The formula is listed below.

=IF('H:\SharedFiles\Compliance\Certification Creation\Senior$afe Training\[WebinarAttendee.xlsx]Sheet0'!A9="Yes",TEXT(IF(CELL("type",'H:\SharedFiles\Compliance\Certification Creation\Senior$afe Training\[WebinarAttendee.xlsx]Sheet0'!$B$5)="l",LEFT('H:\SharedFiles\Compliance\Certification Creation\Senior$afe Training\[WebinarAttendee.xlsx]Sheet0'!$B$5,FIND(" ",'H:\SharedFiles\Compliance\Certification Creation\Senior$afe Training\[WebinarAttendee.xlsx]Sheet0'!$B$5)),INT('H:\SharedFiles\Compliance\Certification Creation\Senior$afe Training\[WebinarAttendee.xlsx]Sheet0'!$B$5)),"mm/dd/yyyy"),"")