Using the day of the week (mon through Fri only) extract from the database the most recent date (mm/dd/yyyy) that corresponds to the request

alleyb

New Member
Joined
Dec 6, 2008
Messages
10
Using Excel 2003 need to get this right in 2003 then will move to 2010

If today is a weekday and Mon then tell me the most recent date that is a Monday
and therefore if the next day is a Tue then give the most recent prior date that corresponds to Tuesday (note so far observation has shown this to be up to 55 days since the last time that Tues existed in the database)
so :
If today is a weekday and Mon then tell me the most recent date that is a Monday
and therefore if we advance today to Wed then give the most recent prior date that corresponds to Wednesday
Answer no 1 is
6/14/1982
Answer no 2 is
5/26/1982

<tbody>
</tbody>
Note that the database currently runs for 3605 lines and increments for the moment manually each day
Month
Date
Open
High
Low
Close
weekday
red
Apr
4/27/1982
120.2
120.35
118.4
118.85
Tuesday
red
Apr
4/28/1982
118.55
119.3
117.7
118.15
Wednesday
red
Apr
4/29/1982
117.5
118.15
117.25
117.6
Thursday
red
Apr
4/30/1982
117.55
118.4
117.4
117.5
Friday
red
May
5/3/1982
117.1
117.5
116.8
117.15
Monday
red
May
5/5/1982
117.85
118.3
117.05
117.55
Wednesday
red
May
5/10/1982
119.2
119.4
118.45
118.5
Monday
red
May
5/12/1982
119.85
120.35
119.05
119.55
Wednesday
red
May
5/13/1982
119.55
119.75
118.15
118.35
Thursday
red
May
5/14/1982
118.55
118.75
118.1
118.3
Friday
red
May
5/17/1982
118.15
118.15
116.5
116.6
Monday
red
May
5/18/1982
116.6
116.7
115.75
116
Tuesday
red
May
5/19/1982
115.95
116.35
114.45
114.65
Wednesday
red
May
5/21/1982
115.25
115.6
114.75
114.9
Friday
red
May
5/25/1982
115.2
115.65
113.45
113.75
Tuesday
red
May
5/26/1982
114
114.1
112
112.5
Wednesday
red
May
5/27/1982
112.45
112.6
111.2
111.85
Thursday
red
May
5/28/1982
111.7
112.5
110.75
111
Friday
red
Jun
6/1/1982
110.75
111.2
109.9
110.05
Tuesday
red
Jun
6/3/1982
111.65
112.05
109.7
110.5
Thursday
red
Jun
6/4/1982
110.4
110.45
108
108.1
Friday
red
Jun
6/8/1982
108.35
109.4
107.3
107.8
Tuesday
red
Jun
6/14/1982
110.5
110.5
108.55
108.55
Monday
red

<tbody>
</tbody>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
=lookup(2,1/(weekday(b2:b24)=weekday(today())),b2:b24)
or you could reference a cell with todays date in it
eg date in a1
=lookup(2,1/(weekday(b2:b24)=weekday(a1)),b2:b24)
 
Upvote 0
Martindwilson. THANKYOU so much for a simple and QUICK reply . Initially I was thrown because today being Sunday (doh!)
Works and give the exact result that I needed. Thankyou agin
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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