How to transform a date table?

[Aziz_86]

Hello, how can i transfor this:

To this:

 

[bosco_yip]

Try,

1] In A8, copied down :

=IFERROR(INDEX(A$3:A$4,MATCH(0,INDEX(--(COUNTIF(A$7:A7,A$3:A$4)=(C$3:C$4-B$3:B$4)+1),0),0)),"")

2] In B8, copied down :

=IF(A8="","",VLOOKUP(A8,A$3:B$4,2,0)+COUNTIF(A$8:A8,A8)-1)

Regards
Bosco

 

[GraH - Guido]

Or a simple Power Query solution

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  ChangeDteToNumber = Table.TransformColumnTypes(Source,{{"To", Int64.Type}, {"From", Int64.Type}}),
  MakeList = Table.AddColumn(ChangeDteToNumber, "ListDatesAsValues", each {[From]..[To]}),
  RemoveDateCols = Table.RemoveColumns(MakeList,{"From", "To"}),
  ExpandedListDatesAsValues = Table.ExpandListColumn(RemoveDateCols, "ListDatesAsValues"),
  ChangeToDteAgain = Table.TransformColumnTypes(ExpandedListDatesAsValues,{{"ListDatesAsValues", type date}})
in
  ChangeToDteAgain

 

[Aziz_86]

View attachment 57799

Try,

1] In A8, copied down :

=IFERROR(INDEX(A$3:A$4,MATCH(0,INDEX(--(COUNTIF(A$7:A7,A$3:A$4)=(C$3:C$4-B$3:B$4)+1),0),0)),"")

2] In B8, copied down :

=IF(A8="","",VLOOKUP(A8,A$3:B$4,2,0)+COUNTIF(A$8:A8,A8)-1)

Regards
Bosco

Mr

View attachment 57799

Try,

1] In A8, copied down :

=IFERROR(INDEX(A$3:A$4,MATCH(0,INDEX(--(COUNTIF(A$7:A7,A$3:A$4)=(C$3:C$4-B$3:B$4)+1),0),0)),"")

2] In B8, copied down :

=IF(A8="","",VLOOKUP(A8,A$3:B$4,2,0)+COUNTIF(A$8:A8,A8)-1)

Regards
Bosco

Thanks Bosco,
Since im still a novice in excel, is it possible to breakdown in plain English how the formula works?

 

[GraH - Guido]

Hi, use the "Evaluate formula" on the ribbon under Formula Auditing section of the Formula tab. It will guide you through the formula step by step.