Find missing dates

[Kasafkhan]

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?

[#2]

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

[#3]

@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)))
)

[#4]

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