How do I convert a table with individual dates to a table with date ranges?

A sample file is attached. On the "Basic table" sheet, there is a basic table with the following columns: ID, Name, Start of leave, End of leave. I need to convert this table to a table that looks like the one on the "New table" sheet.
As you can see, the data is displayed by date ranges (Jan - Apr (Range1), May - Aug (Range2), Sep - Dec (Range3)). I used the following formula to prepare this table:

 = IF (AND ('Basic table'! $ C2> = StartDXX; 'Basic table'! $ C2 <= EndDXX; 'Basic table'! $ D2> = StartDXX; 'Basic table'! $ D2 <= EndDXX); CONCATENATE (TEXT ('Basic table'! $ C2; "dd.mm.yyyy"); "-"; TEXT ('Basic table'! $ D2; "dd.mm.yyyy")); "")

,

wherein XX represents the corresponding numerical designation of the named range, e.g. StartD01, StartD02, etc.

The problem occurs when the start date is in one date range (eg in date range 2 (May - Aug)), and the end date is in another date range (eg in date range 3 (Sep - Dec (Range3)). In this case, the formula does not return anything (which is, of course, correct according to the written formula), and I have to enter the relevant data manually (see ID 29 on the "New table" sheet - marked in red).

I would like to know if there is any better way to prepare the data as shown in the new table?

Could I somehow correct the formula so that even in the cases described above, the data will be displayed correctly, as in the case of a marked record with ID 29?

Would it be possible to prepare such a table also using a Pivot Table?

c2=IF(AND('Basic table'!$D2>=StartD01,'Basic table'!$C2<=EndD01),CONCATENATE(TEXT(MAX(StartD01,'Basic table'!C2),"dd.mm.yyyy")," - ",TEXT(MIN(EndD01,'Basic table'!D2),"dd.mm.yyyy")),"")

d2=IF(AND('Basic table'!$D2>=StartD02,'Basic table'!$C2<=EndD02),CONCATENATE(TEXT(MAX(StartD02,'Basic table'!C2),"dd.mm.yyyy")," - ",TEXT(MIN(EndD02,'Basic table'!D2),"dd.mm.yyyy")),"")

e2=IF(AND('Basic table'!$D2>=StartD03,'Basic table'!$C2<=EndD03),CONCATENATE(TEXT(MAX(StartD03,'Basic table'!C2),"dd.mm.yyyy")," - ",TEXT(MIN(EndD03,'Basic table'!D2),"dd.mm.yyyy")),"")

try the above, copy and paste towards down

Hi samba-ravi!

Great solution, works like a charm...

Thanks...