Get Next and Previous "Load Day" I keep looking at this trying to figure if there's a better way.  All three functions work, but I wanted to get some insights and ideas from others.

IsLoadDay determines if the date parameter is a "Load Day".  Load Days are Tuesday through Saturday, but we skip holidays and designated No Load Days.  There are two existing tables of Holidays (HOLDY_WA_STATE) and No Load Days (afrs_daily_load_no_load_date).

GetNextLoadDay and GetPreviousLoadDay are intended to be used.  They each use IsLoadDay to determine if the date parameter is a Load Day, and then move forward or backward through time until a Load Day is found.

GetNextLoadDay will check the date parameter and determine if it is a Load Day whereas GetPreviousLoadDay will first go one day back and then start checking if the date is a Load Day.

IsLoadDay Function

CREATE FUNCTION [dbo].[IsLoadDay](@Dt DATE)
RETURNS BIT
AS
BEGIN
DECLARE @IsLoadDay BIT = 1
IF DATENAME(DW, @Dt) IN ('SUNDAY', 'MONDAY')
    BEGIN
        SET @IsLoadDay = 0
    END
ELSE
IF EXISTS(SELECT 1 FROM [DBMGMT].[dbo].[afrs_daily_load_no_load_date] WHERE [load_date] = @Dt AND [load_ind] = 'N')
    BEGIN
        SET @IsLoadDay = 0
    END
ELSE
IF EXISTS(SELECT 1 FROM [master].[dbo].[HOLDY_WA_STATE] WHERE [HOLDY_DATE] = @Dt)
    BEGIN
        SET @IsLoadDay = 0
    END
RETURN @IsLoadDay
END
GO


GetNextLoadDay Function

CREATE FUNCTION [dbo].[GetNextLoadDay](@Dt DATE)
RETURNS DATE
AS
BEGIN
DECLARE @NxtDt DATE
SET @NxtDt = @Dt
WHILE [dbo].[IsLoadDay](@NxtDt) != 1 BEGIN
    SET @NxtDt = DATEADD(D, 1, @NxtDt)
END
RETURN @NxtDt
END
GO


GetPreviousLoadDay Function

CREATE FUNCTION [dbo].[GetPreviousLoadDay](@Dt DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @PrvDt DATE
SET @PrvDt = DATEADD(D, -1, @Dt)
WHILE [dbo].[IsLoadDay](@PrvDt) != 1 BEGIN
    SET @PrvDt = DATEADD(D, -1, @PrvDt)
END
RETURN @PrvDt
END
GO
Is Load Day?
Your IsLoadDay function seems a little overcomplicated.
Might I suggest trying something along the lines of this, which brings all the checks you want to do pretty nicely into a single coalesce:
CREATE FUNCTION [dbo].[IsLoadDay](@Dt DATE)
RETURNS BIT
AS
BEGIN

RETURN COALESCE
(
    --If it is a monday or sunday, it's not a load day
    CASE WHEN DATENAME(DW, @Dt) IN ('SUNDAY', 'MONDAY') THEN 0 END,

    --If it is a designated not load day, it's not a load day
    (
        SELECT 0 
        FROM [DBMGMT].[dbo].[afrs_daily_load_no_load_date]
        WHERE [load_date] = @Dt AND [load_ind] = 'N'
    ),
    
    --If it is a holiday, it's not a load day
    (
        SELECT 0
        FROM [master].[dbo].[HOLDY_WA_STATE]
        WHERE [HOLDY_DATE] = @Dt
    ),

    --Otherwise, it's a load day
    1
)
END
GO