#NAME error reported with cell formula

[Ark68]

I have been relying on this worksheet with the formulae in column F for quite some time without issues.
As you can see from some of the data, the formula in column F has worked. But all of a sudden, I'm finding that if I change a value in either columns c or d, I get a #name error from the formula in column F. The formula doesn't appear any different that the results of those formulae in the other cells. I don't know why a change of the cell contents causes the formula to fail. Thoughts?

WS 23-Jul-21.xlsx
	A	B	C	D	E	F
2	LEAD_SP	Kayla	7:00 AM	3:00 PM	LDSP	7:00A - 3:00P
3	CRP	Paul G.	6:30 AM	2:30 PM	CU7	6:30A - 2:30P
4	CWP	Kelly	6:30 AM	2:30 PM	CU6	6:30A - 2:30P
5	CUE1a	Erika	7:00 AM	3:00 PM	CUA	7:00A - 3:00P
6	CUE1b					12:00A - 12:00A
7	CUE2					12:00A - 12:00A
8	CUL1	Chris V.	1:00 PM	10:30 PM	CUB	#NAME?
9	CUL2					12:00A - 12:00A
10	BPE1	James	7:00 AM	3:00 PM	BPA	7:00A - 3:00P
11	BPE2					12:00A - 12:00A
12	BPL1					12:00A - 12:00A
13	BPL2					12:00A - 12:00A
14	HPE1	Ethan D.	7:00 AM	3:00 PM	HPA	7:00A - 3:00P
15	HPE2					12:00A - 12:00A
16	HPL1	Indigo	1:00 PM	9:00 PM	HPC	1:00P - 9:00P
17	HPL2					12:00A - 12:00A
18	RPE1	Danielle	7:00 AM	3:00 PM	RPA	7:00A - 3:00P
19	RPE2					12:00A - 12:00A
20	RPL1	Ethan W.	1:00 PM	9:00 PM	RPC	1:00P - 9:00P
21	RPL2					12:00A - 12:00A
22	WPE1	Nicholas	7:00 AM	3:00 PM	WPA	7:00A - 3:00P
23	WPE2					12:00A - 12:00A
24	WPL1	Owen	2:30 PM	10:30 PM	WPC	2:30P - 10:30P
25	WPL2					12:00A - 12:00A
Staff

Cell Formulas
Range	Formula
F2:F25	F2	=_xlfn.TEXTJOIN(" - ",1,TEXT(C2,"H:MMA/P"),TEXT(D2,"H:MMA/P"))

The data you see is what was on the worksheet when the workbook was loaded. Row 8 illustrates the result when I changed the value in C8.

 

[Fluff]

Looks like you are using the file in xl 2016 which doesn't have the textjoin function.

 

[Ark68]

Ah, that could be. I use 365 at home, but I guess here at school its an older version. Thanks Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[johnnyL]

Fear not @Ark68, If you want to use a UDF, (UserDefinedFunction), you can just replace your formulas and be back in business.

Copy the following code to a module in the workbook:

Function TEXTJOINFunction(Delimiter As String, IgnoreBlank As Boolean, ParamArray arguments() As Variant) As Variant
'
    Dim Argument        As Double
    Dim tmpStr          As String
    Dim ArgumentType    As String
    Dim cel             As Variant
'
    For Argument = 0 To UBound(arguments)
        ArgumentType = TypeName(arguments(Argument))
'
        If ArgumentType = "Range" Or ArgumentType = "Variant()" Then
            For Each cel In arguments(Argument)
                If Not IgnoreBlank = True And Not cel = "" Then tmpStr = tmpStr & CStr(cel) & Delimiter
            Next
        Else
            If IgnoreBlank = True And CStr(arguments(Argument)) = "" Then
'               Blank found so skip this
            Else
                tmpStr = tmpStr & CStr(arguments(Argument)) & Delimiter
            End If
        End If
    Next
'
    If ArgumentType = "Error" Then
        TEXTJOINFunction = CVErr(xlErrNA)
    Else
        tmpStr = IIf(tmpStr = "", Delimiter, tmpStr)
        TEXTJOINFunction = Left(tmpStr, Len(tmpStr) - Len(Delimiter))
    End If
End Function

Then instead of your current formula where you see ',1,':

=_xlfn.TEXTJOIN(" - ",1,TEXT(C2,"H:MMA/P"),TEXT(D2,"H:MMA/P"))

Use the following replacement formula that no longer needs the 'xlfn.' at the beginning of the formula, and uses ',TRUE,' instead of the ',1,' :

=TEXTJOINFunction(" - ",TRUE,TEXT(C2,"H:MMA/P"),TEXT(D2,"H:MMA/P"))

Drag that formula down the column and you should be good to go!