Forum Discussion

Kasafkhan's avatar
Kasafkhan
Copper Contributor
Aug 06, 2022

Find missing dates

Good day to all members,

I am struggling with dates. I attached a file for example. I need to find the missing dates between the dates & report them,

Anyone can please suggest me a way to do it?

excel

3 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Aug 06, 2022

    Kasafkhan 

    Sub missingdates()

Dim i As Long
Dim k As Long
Dim m As Long
Dim n As Long
Dim p As Long
Dim maxrow As Long

maxrow = Range("A" & Rows.Count).End(xlUp).Row

Range("C:C").Clear
n = 1
p = 1
For i = 1 To maxrow

If Cells(i, 1).Value + 1 <> Cells(i + 1, 1) And Cells(i + 1, 1) <> "" Then

k = Cells(i + 1, 1).Value - Cells(i, 1).Value

If k = 2 Then

Cells(n, 3).Value = Cells(i, 1).Value + 1
n = n + 1

Else

For m = 1 To k - 1

Cells(n, 3).Value = Cells(i, 1).Value + p
p = p + 1
n = n + 1
Next m
p = 1

End If

Else
End If

Next i

End Sub

    Maybe with this code. After sorting the dates in column A in ascending order you can click the button in cell F3 in the attached file to run the macro.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 06, 2022

    Kasafkhan 

    It depends on which Excel version/platform you are. If with supporting dynamic arrays that could be

    =LET(
  minDate, MIN( dates ),
  maxDate, MAX( dates),
  allDates, SEQUENCE( maxDate - minDate + 1, , minDate ),
  FILTER( allDates, ISNA(XLOOKUP( allDates, dates, dates)))
)
  • GeorgieAnne's avatar
    GeorgieAnne
    Iron Contributor
    Aug 06, 2022
    Hello Kasafkhan,

    I think what you can do is use an IF statement like this:
    =IF(G3-G2>1,"Missing Date","")
    This will be in cell H3 of Sheet1 in the example workbook you attached.
    Fill down to the end of the data range and anytime you have a missing date it will let you know.

    How does it work:
    MS-Excel handles dates as numbers. Today August 6th 2022 is represented as 44779. That is it has been 44779 days since January 1st 1900. Today at Noon (12:00 PM) the number will be 44779.5 and at 6:00PM it will be 44779.75. So if today is 44779 tomorrow will be 44780 and so on. If the difference between two dates are greater than 1, then there is a missing date in between. To learn more about how MS-Excel handles dates check this wonderful resource: http://www.cpearson.com/excel/datetime.htm

    BUT!!!
    This will only highlight any differences between the dates that is greater than 1, it will not tell you that there maybe multiple missing dates. For explanation in your workbook example you have 4/1/2022 in G9 and 4/4/2022 in G10. The formula will say Missing Date but it will not tell you that there are 2 dates missing April 2nd and April 3rd.
    To correct this you can use this formula:
    =IF(G3-G2>1,CONCATENATE("There are ",G3-G2," Missing date(s)"),"")

    Hope this helps.
    Georgie Anne

Resources