Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling in missing dates
I have a long series of daily data ( 10 years, one line per day) that is
missing a few hundred dates here and there. Is there any way to get Excel to insert blank lines for the missing days so that I have a single line for every day of every year? Thank you! HB |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filling in missing dates
Try this:
In a separate worksheet in that workbook: A1: Date A2: (enter the earliest date you need) A3: =A1+1 copy that cell down until the last cell displays the last date you need. Then B1: Found B2: =ISNUMBER(MATCH(A2,Sheet1!$A$1:$A$3651,0)) Copy that formula down as far as you need. It will tell you if the data was found in the list on Sheet1. Next, DataFilterAutofilter Click on the Found cell and set the criteria to FALSE Select Cell A2, hold down the [Shift] key and press [End] then [Down] EditCopy Switch to Sheet1 Select the cell that is 1 below the last cell of the Date column. EditPaste SpecialValues Now sort your data list (including the appended dates). The missing dates will find their way into the data. (Of course, adjust sheet names and ranges to suit your situation) Does that help? *********** Regards, Ron "picklet222" wrote: I have a long series of daily data ( 10 years, one line per day) that is missing a few hundred dates here and there. Is there any way to get Excel to insert blank lines for the missing days so that I have a single line for every day of every year? Thank you! HB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTables - Group Dates, excluding dates | Excel Discussion (Misc queries) | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
Auto filling dates on other worksheets with data from a next payme | Excel Worksheet Functions | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |