Reg Excel Formula

Question

Is there a formula to find consecutive dates in a row in excel

Answer 1

Anitha,

I looked into your question, and it seems like there is a way you can do it through the conditional formatting tab. As much as I would like to take credit for it I simply found the answer through the following article https://www.extendoffice.com/documents/excel/3563-excel-highlight-consecutive-numbers.html

With that said, to simplify the whole article’s contents you can do the following: Go to the Home Tab and click on Conditional Formatting (it’s within the Styles section of the ribbon). After that go down to the very bottom and click on “New Rule.”

After this a new menu will pop up. You should click on the last option that says, “Use a formula to determine which cells to format.” This should open an option for you to put in an equation which you should copy and paste this equation: =A1+1=A2

Be sure to also click the “Format…” button to insert a color into the document and after that click “Ok” and “Ok” again.

Now with this method there are two things that you need to know. First, it will only highlight the A1 that has a consecutive date. Meaning that if you have two dates like 2/2/2022 and 2/3/2022 then it will only highlight 2/2/2022 showing you that the next number is consecutive. (I tried to do the same equation but subtracting and reversing the process so you can have all numbers highlighted, but it didn’t work for some reason).

The second that I wanted to let you know about this method is that it requires the dates to have been previously sorted from “Oldest to Newest” which you can find on the Home tab in the button labeled “Sort and Filter.”

I hope this helps. I enjoyed looking into the problem for you. Let me know if there’s any other questions that you have!

Good luck!

Josh