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

L

LuGyver

Board Regular
Joined
Mar 13, 2014
Messages
88
Office Version
  1. 2007
Platform
  1. Windows
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

A
B
C
D
E
F
G
H
7/6/2018
3
8
4
7/5/2018
1
9
4
7/4/2018
4
5
1
7/3/2018
1
6
7
7/2/2018
5
3
3
7/1/2018
6
4
2
64
62
42
6/30/2018
5
2
9

<tbody>
</tbody>

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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
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>
 
Upvote 0
AB
C
D
E
F
G
1
06/07/2019384 646242
205/07/2019194
304/07/2019451
403/07/2019167
502/07/2019533
601/07/2019642
730/06/2019529

<tbody>
</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
 
Last edited:
Upvote 0
LuGyver said:
ABCDEFG
106/07/2019384 646242
205/07/2019194
304/07/2019451
403/07/2019167
502/07/2019533
601/07/2019642
730/06/2019529

<tbody>
</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
Click to expand...

You can help me by showing the results in all columns F, G and H
 
Upvote 0
Is this what you mean?
ABCDEFGH
7/6/2018 384
64
6242
7/5/2018 19464
62
42
7/4/2018 451
64
6242
7/3/2018
1
6
7
64
62
42
7/2/2018
5
3
3
64
62
42
7/1/2018
6
4
2
52
5929
6/30/2018
5
2
9

<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>
</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
 
Upvote 0
Figured it out
Thanks for your help
Cell
Formula
F
=OFFSET($B7,VALUE(DAY($B7))-1,2,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,3,1,1)
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>
</tbody>
 
Upvote 0
LuGyver said:
Figured it out
Thanks for your help
CellFormula
F=OFFSET($B7,VALUE(DAY($B7))-1,2,1,1)&OFFSET($B7,VALUE(DAY($B7))-1,3,1,1)
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>
</tbody>
Click to expand...

I'm glad to know you solved it. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
INDEX MATCH
Replies
1
Views
172
Fluff
Fluff
J
  • Solved
Help with calculation between work days based on condition
Replies
7
Views
253
Fluff
Fluff
J
  • Solved
IFS
Replies
4
Views
86
Peter_SSs
Peter_SSs
K
  • Question
Fill any five cell only
Replies
3
Views
173
Peter_SSs
Peter_SSs
K
  • Question
conditional formatting question
Replies
2
Views
149
kerm007
K

Forum statistics

Threads
1,214,515
Messages
6,119,973
Members
448,933
Latest member
Bluedbw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top