Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find missing dates
I am looking for code that delivers a list of missing
dates from a single column. I am not sure if there is an easier way to do this with out creating a record set. Keep in mind that I am a new code programmer with excel. If you have help, please let me know |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find missing dates
What determines that a date is missing?
You could use a simple match against a list of should be dates and the other list to see if it is there, like =IF(COUNTIF(A:A, H1)0,"","Missing") and copy down all dates to check. -- HTH RP (remove nothere from the email address if mailing direct) "Wally" wrote in message ... I am looking for code that delivers a list of missing dates from a single column. I am not sure if there is an easier way to do this with out creating a record set. Keep in mind that I am a new code programmer with excel. If you have help, please let me know |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find missing dates
i am trying to creat a sub list of dates that are not contained in the first
list. i have a list of dates. say 1/105 through today. however, there might be some dates missing in the list. i would like to create a list of those dates on a form. if i need to do this on the worksheet i can. any advise would be helpful. "Bob Phillips" wrote: What determines that a date is missing? You could use a simple match against a list of should be dates and the other list to see if it is there, like =IF(COUNTIF(A:A, H1)0,"","Missing") and copy down all dates to check. -- HTH RP (remove nothere from the email address if mailing direct) "Wally" wrote in message ... I am looking for code that delivers a list of missing dates from a single column. I am not sure if there is an easier way to do this with out creating a record set. Keep in mind that I am a new code programmer with excel. If you have help, please let me know |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find missing dates
Assume dates are on sheet1 starting in A2 with a contiguous block of cells
with no blanks, just unique dates in sequential order with some missing. In the initialize event of a userform with a Listbox named listbox1 (with no rowsource assignment): Private Sub UserForm_Initialize() Dim rng As Range, cell As Range Dim i As Long Me.ListBox1.Clear With Worksheets("Sheet1") Set rng = .Range(.Range("A2"), .Range("A2").End(xlDown)) End With For Each cell In rng If cell.Row = rng(rng.Count).Row Then _ Exit Sub i = 1 Do While CLng(cell(2).Value2) < _ CLng(cell.Value2) + i Me.ListBox1.AddItem Format(CLng(cell.Value2) _ + i, "mm/dd/yyyy") i = i + 1 Loop Next End Sub -- Regards, Tom Ogilvy "wally" wrote in message ... i am trying to creat a sub list of dates that are not contained in the first list. i have a list of dates. say 1/105 through today. however, there might be some dates missing in the list. i would like to create a list of those dates on a form. if i need to do this on the worksheet i can. any advise would be helpful. "Bob Phillips" wrote: What determines that a date is missing? You could use a simple match against a list of should be dates and the other list to see if it is there, like =IF(COUNTIF(A:A, H1)0,"","Missing") and copy down all dates to check. -- HTH RP (remove nothere from the email address if mailing direct) "Wally" wrote in message ... I am looking for code that delivers a list of missing dates from a single column. I am not sure if there is an easier way to do this with out creating a record set. Keep in mind that I am a new code programmer with excel. If you have help, please let me know |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find missing dates
"wally" wrote in message
... i am trying to creat a sub list of dates that are not contained in the first list. i have a list of dates. say 1/105 through today. however, there might be some dates missing in the list. i would like to create a list of those dates on a form. if i need to do this on the worksheet i can. have you tried "match()" function let column_a contains your dates, column_b all range you test for existence, column_c put formula: =match(b1,a$1:a$999,0) this formula will return either found position, or "error" for missing date |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find a record with a missing value | Excel Discussion (Misc queries) | |||
compare 2 tables of dates to find the preceding dates | Excel Worksheet Functions | |||
How to fill missing cells in the collumn of dates? | New Users to Excel | |||
Find the missing number? | Excel Discussion (Misc queries) | |||
Filling in missing dates | Excel Worksheet Functions |