Transform rows in columns by Day Name

You weren't far off, but you're grouping by the wrong thing. The Row Number is what you want in each row, but you've defined it as your columns

WITH Days AS
    (SELECT day_name,
            day_date,
            RANK() OVER (PARTITION BY day_name ORDER BY day_date) AS [rn]
     FROM dbo.ISP_Cloud_DaysFromSession)
SELECT MAX(CASE day_name WHEN 'Monday' THEN day_date END) AS Monday,
       MAX(CASE day_name WHEN 'Tuesday' THEN day_date END) AS Tuesday
FROM Days
GROUP BY rn
ORDER BY rn ASC;

You can also use row_number and pivot:

WITH Days AS (
  SELECT ROW_NUMBER() OVER (PARTITION BY day_name ORDER BY day_date) rr
    , day_name, day_date
  FROM ISP_Cloud_DaysFromSession
)
SELECT [Monday], [Tuesday]
FROM (
  SELECT *
  FROM Days
) AS SOURCE
PIVOT (
  MAX (day_date)
  FOR day_name IN ([Monday], [Tuesday])
) AS pivotable