Modify and transpose large dataset in Excel

Question

I am trying to convert a database that consists of hourly data on annual basis from 00:00 (hour) 01 (month) 2015 (year) to 00:00, 01-12-2021.

Default format of the table:

In the default format, one column contains both hour and date and the second contains the value. There are more than 60000 rows.

What I expect:

In the expected format, the first column contains only the date while the first row contains the hours. I want to write a formula (maybe with transpose?) that transfers for each date and hour the value to the new cell.
For example, for 02-01-2021, at 01:00, the corresponding value is 52.42. To do this I click "TRANSPOSE(AD27:AD50)" and the row is filled. AD is the column location of the default format.

Unfortunately when I click with my cursor to the 3rd row (where 02-01-2021 is located) and drop it down to pass it to the next row, Excel does not recognize the pattern.
While Excel should start from the last selected column and transpose the next 23 cells (AD51:AD74) it goes like this (AD28:AD51).

In the third picture you see:

• With the red pen: The old format. Date and time are in the same cell (column AC).

• With the black pen: Date has been split from time. Date is in a column A while time is in row 1.

• With the blue pen: The cells I want to fill by transferring the data listed in the table (pinpointed by the red pen).

Answer 1

In version 2202, you should have the more recent functions, so a simple method would be:

• select some cell in your date/time table
  • Home/Format as table Then:

    E2:  Enter a list of the dates only from your table.
           *This can be done in various ways*
    F1:  Enter a list of the hours from left to right (or use the `SEQUENCE` function to generate them)
    F2:  =TRANSPOSE(FILTER(Table1[Value],   DATEVALUE(TEXT(Table1[DateTime],"m/d/yyy"))=$E2,""))  and fill down as far as needed

The results will spill right.