Find Missing Day of Month

gguardia

New Member
Joined
Feb 21, 2002
Messages
15
Column D has a list of the dates the market was open, column K has the market close on that date. I would like to find the value of the market close on, say, every 14th of the month. However, if the market is closed on the 14th (weekend, holiday, the week following 9/11, for example), I want the the value of the market close on the next day the market is open.

Example: 1/14/1928 was a Saturday, so I want the value on the next open day, which was Monday, 1/16/1928.
Example: 7/14/1977, a Thursday, the market was closed, but was opened on Friday 7/15/1977, so I want the value on that Friday.
Example: 9/14/2001 the market was closed. The next day the market was open was 9/17/2001, so I would like the value on that day.

How can I do this using formulas only, without using VBA?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,123
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I am not sure what version you are using, but if one with XLOOKUP then maybe
iqcorr.xlsx
AKLMN
1DateMkt CloseDate14-Sep-01
201-Sep-01₦116.00Mkt Close₦126.00
303-Sep-01₦107.00
404-Sep-01₦125.00
505-Sep-01₦137.00
606-Sep-01₦103.00
707-Sep-01₦130.00
810-Sep-01₦128.00
911-Sep-01₦120.00
1012-Sep-01₦112.00
1113-Sep-01₦127.00
1217-Sep-01₦126.00
1318-Sep-01₦134.00
1419-Sep-01₦113.00
1520-Sep-01₦128.00
1621-Sep-01₦127.00
1724-Sep-01₦114.00
1825-Sep-01₦120.00
1926-Sep-01₦103.00
2027-Sep-01₦109.00
2128-Sep-01₦139.00
Sheet4
Cell Formulas
RangeFormula
N2N2=XLOOKUP(N1,$A$2:$A$21,$K$2:$K$21,,1)
 
Solution

gguardia

New Member
Joined
Feb 21, 2002
Messages
15
Thank you, Momentman, that is exactly what I was looking for! And I now know a new function I was not aware of.
 

gguardia

New Member
Joined
Feb 21, 2002
Messages
15
Ok, it was working perfectly the first day. When I reopened the spreadsheet the following day, the results are all for the same day, over a year in advance. I can not figure out why it is not working!

S&P500 Daily Data.xlsx
ABCDEFGHJKLMN
1DateOpenHighLowClose*Adj Close**VolumeDay of month:14
2Jan 03, 192817.7617.7617.7617.7617.76
3Jan 04, 192817.7217.7217.7217.7217.72-01/14/1928Apr 01, 192924.88
4Jan 05, 192817.5517.5517.5517.5517.55-02/14/1928Apr 01, 192924.88
5Jan 06, 192817.6617.6617.6617.6617.66-03/14/1928Apr 01, 192924.88
6Jan 09, 192817.517.517.517.517.5-04/14/1928Apr 01, 192924.88
7Jan 10, 192817.3717.3717.3717.3717.37-
8Jan 11, 192817.3517.3517.3517.3517.35-
9Jan 12, 192817.4717.4717.4717.4717.47-
10Jan 13, 192817.5817.5817.5817.5817.58-
11Jan 16, 192817.2917.2917.2917.2917.29-
12Jan 17, 192817.317.317.317.317.3-
13Jan 18, 192817.2617.2617.2617.2617.26-
14Jan 19, 192817.3817.3817.3817.3817.38-
15Jan 20, 192817.4817.4817.4817.4817.48-
16Jan 23, 192817.6417.6417.6417.6417.64-
17Jan 24, 192817.7117.7117.7117.7117.71-
18Jan 25, 192817.5217.5217.5217.5217.52-
19Jan 26, 192817.6317.6317.6317.6317.63-
20Jan 27, 192817.6917.6917.6917.6917.69-
21Jan 30, 192817.4917.4917.4917.4917.49-
22Jan 31, 192817.5717.5717.5717.5717.57-
23Feb 01, 192817.5317.5317.5317.5317.53-
24Feb 02, 192817.6317.6317.6317.6317.63-
25Feb 03, 192817.417.417.417.417.4-
26Feb 06, 192817.4517.4517.4517.4517.45-
27Feb 07, 192817.4417.4417.4417.4417.44-
28Feb 08, 192817.4917.4917.4917.4917.49-
29Feb 09, 192817.5517.5517.5517.5517.55-
30Feb 10, 192817.5417.5417.5417.5417.54-
31Feb 14, 192817.4417.4417.4417.4417.44-
32Feb 15, 192817.417.417.417.417.4-
33Feb 16, 192817.3517.3517.3517.3517.35-
34Feb 17, 192817.0417.0417.0417.0417.04-
35Feb 20, 192816.9516.9516.9516.9516.95-
36Feb 21, 192817.1117.1117.1117.1117.11-
37Feb 23, 192817.1317.1317.1317.1317.13-
38Feb 24, 192817.1817.1817.1817.1817.18-
39Feb 27, 192817.1117.1117.1117.1117.11-
40Feb 28, 192817.1617.1617.1617.1617.16-
41Feb 29, 192817.2617.2617.2617.2617.26-
42Mar 01, 192817.317.317.317.317.3-
43Mar 02, 192817.317.317.317.317.3-
44Mar 05, 192817.5717.5717.5717.5717.57-
45Mar 06, 192817.6717.6717.6717.6717.67-
46Mar 07, 192817.5817.5817.5817.5817.58-
47Mar 08, 192817.6417.6417.6417.6417.64-
48Mar 09, 192817.9317.9317.9317.9317.93-
49Mar 12, 19281818181818-
50Mar 13, 192817.9217.9217.9217.9217.92-
51Mar 14, 192817.9317.9317.9317.9317.93-
52Mar 15, 192818.0718.0718.0718.0718.07-
53Mar 16, 192818.2618.2618.2618.2618.26-
54Mar 19, 192818.3618.3618.3618.3618.36-
55Mar 20, 192818.4618.4618.4618.4618.46-
56Mar 21, 192818.6518.6518.6518.6518.65-
57Mar 22, 192818.618.618.618.618.6-
58Mar 23, 192818.7818.7818.7818.7818.78-
59Mar 26, 192819.0519.0519.0519.0519.05-
60Mar 27, 192818.9418.9418.9418.9418.94-
61Mar 28, 192818.8918.8918.8918.8918.89-
62Mar 29, 192818.9518.9518.9518.9518.95-
63Mar 30, 192819.2819.2819.2819.2819.28-
64Apr 02, 192818.9118.9118.9118.9118.91-
65Apr 03, 192819.0219.0219.0219.0219.02-
66Apr 04, 192818.9918.9918.9918.9918.99-
67Apr 05, 192819.2319.2319.2319.2319.23-
68Apr 09, 192819.2719.2719.2719.2719.27-
69Apr 10, 192819.119.119.119.119.1-
70Apr 11, 192819.4419.4419.4419.4419.44-
71Apr 12, 192819.4119.4119.4119.4119.41-
72Apr 13, 192819.7319.7319.7319.7319.73-
73Apr 16, 192819.6819.6819.6819.6819.68-
74Apr 17, 192819.5219.5219.5219.5219.52-
75Apr 18, 192819.5219.5219.5219.5219.52-
76Apr 19, 192819.6319.6319.6319.6319.63-
77Apr 20, 192819.3419.3419.3419.3419.34-
78Apr 23, 192819.1519.1519.1519.1519.15-
79Apr 24, 192819.2519.2519.2519.2519.25-
80Apr 25, 192819.3319.3319.3319.3319.33-
81Apr 26, 192819.4719.4719.4719.4719.47-
82Apr 27, 192819.6819.6819.6819.6819.68-
83Apr 30, 192819.7519.7519.7519.7519.75-
84May 01, 192819.7819.7819.7819.7819.78-
S&P500Data
Cell Formulas
RangeFormula
L3L3=DATE(1928,1,$M$1)
M3M3=XLOOKUP($L3,$A:$A,$A:$A,,1)
N3:N6N3=XLOOKUP($L3,$A:$A,$F:$F,,1)
L4:L6L4=EDATE(L3,1)
M4:M6M4=XLOOKUP($L4,$A:$A,$A:$A,,1,1)
Named Ranges
NameRefers ToCells
'S&P500Data'!_FilterDatabase='S&P500Data'!$A$1:$G$23447M3:N6
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,123
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
i noticed that the dates in Column A are not actually dates. so by ensuring they are dates, you should egt it to work

iqcorr.xlsx
ABCDEFGHIJKLM
1DateOpenHighLowClose*Adj Close**VolumeDay of month:14
2Jan 03, 192817.7617.7617.7617.7617.761020
3Jan 04, 192817.7217.7217.7217.7217.72-102114-Jan-2816-Jan-28
4Jan 05, 192817.5517.5517.5517.5517.55-102214-Feb-2814-Feb-28
5Jan 06, 192817.6617.6617.6617.6617.66-102314-Mar-2814-Mar-28
6Jan 09, 192817.517.517.517.517.5-102614-Apr-2816-Apr-28
7Jan 10, 192817.3717.3717.3717.3717.37-1027
8Jan 11, 192817.3517.3517.3517.3517.35-1028
9Jan 12, 192817.4717.4717.4717.4717.47-1029
10Jan 13, 192817.5817.5817.5817.5817.58-1020
11Jan 16, 192817.2917.2917.2917.2917.29-1023
12Jan 17, 192817.317.317.317.317.3-1024
13Jan 18, 192817.2617.2617.2617.2617.26-1025
14Jan 19, 192817.3817.3817.3817.3817.38-1026
15Jan 20, 192817.4817.4817.4817.4817.48-1027
16Jan 23, 192817.6417.6417.6417.6417.64-1020
17Jan 24, 192817.7117.7117.7117.7117.71-1021
18Jan 25, 192817.5217.5217.5217.5217.52-1022
19Jan 26, 192817.6317.6317.6317.6317.63-1023
20Jan 27, 192817.6917.6917.6917.6917.69-1024
21Jan 30, 192817.4917.4917.4917.4917.49-1027
22Jan 31, 192817.5717.5717.5717.5717.57-1028
23Feb 01, 192817.5317.5317.5317.5317.53-1029
24Feb 02, 192817.6317.6317.6317.6317.63-1020
25Feb 03, 192817.417.417.417.417.4-1021
26Feb 06, 192817.4517.4517.4517.4517.45-1024
27Feb 07, 192817.4417.4417.4417.4417.44-1025
28Feb 08, 192817.4917.4917.4917.4917.49-1026
29Feb 09, 192817.5517.5517.5517.5517.55-1027
30Feb 10, 192817.5417.5417.5417.5417.54-1028
31Feb 14, 192817.4417.4417.4417.4417.44-1022
32Feb 15, 192817.417.417.417.417.4-1023
33Feb 16, 192817.3517.3517.3517.3517.35-1024
34Feb 17, 192817.0417.0417.0417.0417.04-1025
35Feb 20, 192816.9516.9516.9516.9516.95-1028
36Feb 21, 192817.1117.1117.1117.1117.11-1029
37Feb 23, 192817.1317.1317.1317.1317.13-1021
38Feb 24, 192817.1817.1817.1817.1817.18-1022
39Feb 27, 192817.1117.1117.1117.1117.11-1025
40Feb 28, 192817.1617.1617.1617.1617.16-1026
41Feb 29, 192817.2617.2617.2617.2617.26-1027
42Mar 01, 192817.317.317.317.317.3-1028
43Mar 02, 192817.317.317.317.317.3-1029
44Mar 05, 192817.5717.5717.5717.5717.57-1022
45Mar 06, 192817.6717.6717.6717.6717.67-1023
46Mar 07, 192817.5817.5817.5817.5817.58-1024
47Mar 08, 192817.6417.6417.6417.6417.64-1025
48Mar 09, 192817.9317.9317.9317.9317.93-1026
49Mar 12, 19281818181818-1029
50Mar 13, 192817.9217.9217.9217.9217.92-1030
51Mar 14, 192817.9317.9317.9317.9317.93-1031
52Mar 15, 192818.0718.0718.0718.0718.07-1032
53Mar 16, 192818.2618.2618.2618.2618.26-1033
54Mar 19, 192818.3618.3618.3618.3618.36-1036
55Mar 20, 192818.4618.4618.4618.4618.46-1037
56Mar 21, 192818.6518.6518.6518.6518.65-1038
57Mar 22, 192818.618.618.618.618.6-1039
58Mar 23, 192818.7818.7818.7818.7818.78-1030
59Mar 26, 192819.0519.0519.0519.0519.05-1033
60Mar 27, 192818.9418.9418.9418.9418.94-1034
61Mar 28, 192818.8918.8918.8918.8918.89-1035
62Mar 29, 192818.9518.9518.9518.9518.95-1036
63Mar 30, 192819.2819.2819.2819.2819.28-1037
64Apr 02, 192818.9118.9118.9118.9118.91-1030
65Apr 03, 192819.0219.0219.0219.0219.02-1031
66Apr 04, 192818.9918.9918.9918.9918.99-1032
67Apr 05, 192819.2319.2319.2319.2319.23-1033
68Apr 09, 192819.2719.2719.2719.2719.27-1037
69Apr 10, 192819.119.119.119.119.1-1038
70Apr 11, 192819.4419.4419.4419.4419.44-1039
71Apr 12, 192819.4119.4119.4119.4119.41-1030
72Apr 13, 192819.7319.7319.7319.7319.73-1031
73Apr 16, 192819.6819.6819.6819.6819.68-1034
74Apr 17, 192819.5219.5219.5219.5219.52-1035
75Apr 18, 192819.5219.5219.5219.5219.52-1036
76Apr 19, 192819.6319.6319.6319.6319.63-1037
77Apr 20, 192819.3419.3419.3419.3419.34-1038
78Apr 23, 192819.1519.1519.1519.1519.15-1031
79Apr 24, 192819.2519.2519.2519.2519.25-1032
80Apr 25, 192819.3319.3319.3319.3319.33-1033
81Apr 26, 192819.4719.4719.4719.4719.47-1034
82Apr 27, 192819.6819.6819.6819.6819.68-1035
83Apr 30, 192819.7519.7519.7519.7519.75-1038
84May 01, 192819.7819.7819.7819.7819.78-1039
Sheet9
Cell Formulas
RangeFormula
H2:H84H2=LEFT(A2,3)
I2:I84I2=MID(A2,5,2)
L3L3=DATE(1928,1,$M$1)
M3:M6M3=XLOOKUP(L3,$A$2:$A$84,$A$2:$A$84,,1)
L4:L6L4=EDATE(L3,1)
 

gguardia

New Member
Joined
Feb 21, 2002
Messages
15
Thank you again, Momentman! I usually catch things like that. Odd that it worked at first. And then once again briefly.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,403
Messages
5,769,872
Members
425,576
Latest member
niall54321

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
Top