# excel dates quicker way to work with missing dates

#### jimjohn

##### Board Regular
i have one column(A) that includes all dates from june 2003 till june 2008 (but only weekdays are included).

i have another column(C) that includes most of these dates (but some days are missing), and then beside this column is another one (D) with the rates corresponding to those dates in column C.

what i want to do is, for each missing date (weekdays only), i want to take an average of the rate on the day before and the rate the day after.

is there a quick way to do this? right now, ive just been matching each date on column A with column C, returning a 1 if theyre the same date and a 0 otherwise. So when I see a 1, I copy over the rates corresponding to those rows in D over to my new column. and whenever i see a 0, i calculate the average and then move the rest of Column C and D down one.

its not so much the average calulations that take a while, its this whole process of finding a 0 and then each time i do, i have to move the rest of the columns down one and then i have to change my match formula. any ideas of a quicker way to do this? thanks!

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Domski

##### Well-known Member
Hi,

It's not very elegant but:

- Create your list of weedays Jun 03-Jun 08 using series fill
- Use vlookup to bring the rates into that list
- Paste special values and Find...replace any 0's or #N/A
- Select the rates column and press F5...Special...Blanks
- Enter your formula e.g. =AVERAGE(A1,A3) and confirm with Ctrl+Enter

You get some circular references if there's 2 consecutive days without a rate but you can either change the calculation iterations in options or deal with them manually.

Dom

#### Gerald Higgins

##### Well-known Member
Yes there are quicker ways.

First, use a VLOOKUP formula, to cross reference all the rates in Col D to the relevant date in Col A.
Like this
Code:
``=if(isna(vlookup(a1,c\$1:d\$100,2,false)),"",vlookup(a1,c\$1:d\$100,2,false)``
If the date in Col A is found in Col C, this will return the relevant rate from Col D.
If the date in Col A is NOT found in Col C, this will return a blank.

Then, you can write a formula to fill in the gaps for missing rates.
For example, let's say you've put my suggested formula in Col E.
Use something like this
Code:
``=if(E2="",(e1+e3)/2,+e2)``
This will work as long as you only have single days missing. If you have strings of several days missing together, you'll need something more complicated.

#### barry houdini

##### MrExcel MVP
This formula in B1 copied down will find the value from column D that corresponds to the date in column A, if that date doesn't exist [in column C] it gives the average of the previous date and the next date, whatever the gap

=(LOOKUP(A1,C\$1:D\$1300)+INDEX(D\$1:D\$1300,MATCH(A1,C\$1:C\$1300)+(LOOKUP(A1,C\$1:C\$1300)<>A1)))/2

Replies
2
Views
549
Replies
0
Views
249
Replies
19
Views
505
Replies
5
Views
197
Replies
3
Views
141

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.

1,163,951
Messages
5,834,543
Members
430,295
Latest member
amdis

### 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.

### Which adblocker are you using?

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

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