find date in past based on dates day number the show show cell value in same row

[LuGyver]

Results to show in columns F,G and H. Please if possible don't use VB scripts
I would like excel to look at the days number in column A (7/6/2018 = 6), then go back that many days (including the day). For example: In Column A, If today is the 6th, then go back 6th,5th,4th,3rd,2nd, to the 1st.

If that is possible, then I also need it to combine cell values in C,D,E (in the same row) as C&D, C&E, D&E

[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD]7/6/2018
[/TD]
[TD][/TD]
[TD]3
[/TD]
[TD]8
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/5/2018
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]9
[/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/4/2018
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/3/2018
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/2/2018
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7/1/2018
[/TD]
[TD][/TD]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD="align: center"]64
[/TD]
[TD="align: center"]62
[/TD]
[TD="align: center"]42
[/TD]
[/TR]
[TR]
[TD]6/30/2018
[/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, the formula results in columns F,G and H would be F = 64 G = 62 H = 42
Albeit strange, I hope this is sensible to excel
Thank you

 

[DanteAmor]

Does it mean that if it is day 1, the concatenations must be made?

Try this
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">06/07/2019</td><td > </td><td style="text-align:right; ">3</td><td style="text-align:right; ">8</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">05/07/2019</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">9</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">04/07/2019</td><td > </td><td style="text-align:right; ">4</td><td style="text-align:right; ">5</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">03/07/2019</td><td > </td><td style="text-align:right; ">1</td><td style="text-align:right; ">6</td><td style="text-align:right; ">7</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">02/07/2019</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">01/07/2019</td><td > </td><td style="text-align:right; ">6</td><td style="text-align:right; ">4</td><td style="text-align:right; ">2</td><td style="text-align:right; ">64</td><td style="text-align:right; ">62</td><td style="text-align:right; ">42</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">30/06/2019</td><td > </td><td style="text-align:right; ">5</td><td style="text-align:right; ">2</td><td style="text-align:right; ">9</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >F6</td><td >=IF(DAY($A6)=1,C6&D6)</td></tr><tr><td >G6</td><td >=IF(DAY($A6)=1,C6&E6)</td></tr><tr><td >H6</td><td >=IF(DAY($A6)=1,D6&E6)</td></tr></table></td></tr></table>

 

[LuGyver]

A	B		C	D	E	F	G
	64	62	42

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"]06/07/2019[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]05/07/2019[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]04/07/2019[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]03/07/2019[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]02/07/2019[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/07/2019[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]30/06/2019[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]

</tbody>

Yes include the 1st in the count. My mistake after looking. the formula should show results in the row the day count starts as shown above

 

[DanteAmor]

A	B		C	D	E	F	G
	64	62	42

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]06/07/2019[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]05/07/2019[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]04/07/2019[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]03/07/2019[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]02/07/2019[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]01/07/2019[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]30/06/2019[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]

</tbody>

Yes include the 1st in the count. My mistake after looking. the formula should show results in the row the day count starts as shown above

You can help me by showing the results in all columns F, G and H

 

[LuGyver]

Is this what you mean?

<colgroup><col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" width="64" span="7"> </colgroup><tbody>
[TD="class: xl65, width: 80"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]

[TD="class: xl66, width: 80"]7/6/2018[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl68, width: 64"]3[/TD]
[TD="class: xl68, width: 64"]8[/TD]
[TD="class: xl68, width: 64"]4
[/TD]
[TD="class: xl68, width: 64"]64
[/TD]
[TD="class: xl68, width: 64"]62[/TD]
[TD="class: xl68, width: 64"]42[/TD]

[TD="class: xl66, width: 80"]7/5/2018[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl68, width: 64"]1[/TD]
[TD="class: xl68, width: 64"]9[/TD]
[TD="class: xl68, width: 64"]4[/TD]
[TD="class: xl67, width: 64"]64
[/TD]
[TD="class: xl67, width: 64"]62
[/TD]
[TD="class: xl67, width: 64"]42
[/TD]

[TD="class: xl66, width: 80"]7/4/2018[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl68, width: 64"]4[/TD]
[TD="class: xl68, width: 64"]5[/TD]
[TD="class: xl68, width: 64"]1
[/TD]
[TD="class: xl67, width: 64"]64
[/TD]
[TD="class: xl67, width: 64"]62[/TD]
[TD="class: xl67, width: 64"]42[/TD]

[TD="class: xl66, width: 80"]7/3/2018
[/TD]
[TD="class: xl67, width: 64"]
[/TD]
[TD="class: xl68, width: 64"]1
[/TD]
[TD="class: xl68, width: 64"]6
[/TD]
[TD="class: xl68, width: 64"]7
[/TD]
[TD="class: xl67, width: 64"]64
[/TD]
[TD="class: xl67, width: 64"]62
[/TD]
[TD="class: xl67, width: 64"]42
[/TD]

[TD="class: xl66, width: 80"]7/2/2018
[/TD]
[TD="class: xl67, width: 64"]
[/TD]
[TD="class: xl68, width: 64"]5
[/TD]
[TD="class: xl68, width: 64"]3
[/TD]
[TD="class: xl68, width: 64"]3
[/TD]
[TD="class: xl67, width: 64"]64
[/TD]
[TD="class: xl67, width: 64"]62
[/TD]
[TD="class: xl67, width: 64"]42
[/TD]

[TD="class: xl66, width: 80"]7/1/2018
[/TD]
[TD="class: xl67, width: 64"]
[/TD]
[TD="class: xl68, width: 64"]6
[/TD]
[TD="class: xl68, width: 64"]4
[/TD]
[TD="class: xl68, width: 64"]2
[/TD]
[TD="class: xl68, width: 64"]52
[/TD]
[TD="class: xl68, width: 64"]59[/TD]
[TD="class: xl68, width: 64"]29
[/TD]

[TD="class: xl66, width: 80"]6/30/2018
[/TD]
[TD="class: xl67, width: 64"]
[/TD]
[TD="class: xl68, width: 64"]5
[/TD]
[TD="class: xl68, width: 64"]2
[/TD]
[TD="class: xl68, width: 64"]9
[/TD]
[TD="class: xl67, width: 64"]
[/TD]
[TD="class: xl67, width: 64"] [/TD]
[TD="class: xl67, width: 64"] [/TD]

</tbody>

There are more in descending order but this should be how it looks. Right now I am trying different variations of OFFSET() function but nothing yet

 

[LuGyver]

Figured it out
Thanks for your help

F
G	=OFFSET($B7,VALUE(DAY($B7))-1,2,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,4,1,1)
H	=OFFSET($B7,VALUE(DAY($B7))-1,3,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,4,1,1)

<tbody>
[TD="width: 128"]Cell
[/TD]
[TD="width: 592"]Formula[/TD]

[TD="class: xl78"]=OFFSET($B7,VALUE(DAY($B7))-1,2,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,3,1,1)[/TD]

</tbody>

 

[DanteAmor]

Figured it out
Thanks for your help

Cell
F
G	=OFFSET($B7,VALUE(DAY($B7))-1,2,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,4,1,1)
H	=OFFSET($B7,VALUE(DAY($B7))-1,3,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,4,1,1)

<tbody>
[TD="width: 592"]Formula[/TD]

[TD="class: xl78"]=OFFSET($B7,VALUE(DAY($B7))-1,2,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,3,1,1)[/TD]

</tbody>

I'm glad to know you solved it. Thanks for the feedback.