Find missing dates

Copper Contributor

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?

3 Replies
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:

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


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

  minDate, MIN( dates ),
  maxDate, MAX( dates),
  allDates, SEQUENCE( maxDate - minDate + 1, , minDate ),
  FILTER( allDates, ISNA(XLOOKUP( allDates, dates, dates)))


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

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


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

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.