Google sheets not recognising a date as a date

Question

On this report I am trying to do some date analysis.

In column G I need to pull all the URLs from column A which have a closing date in the next 7 days. And in column I, I need all the URLs which the closing date is in the past.

The issue is that when I try to reference the Closing dates in column E it does not read and as such work.

Could I get some help on making sheets read Column E properly and also some help on the formulas I need in Column G and I

Thank you

Answer 1

You might want to look at the DATEVALUE function:

=DATEVALUE(D2)

If cell D2 contains a date in the form of text (such as "26 May 2022"), this formula will return the date as a number. (You can then format the cell to display a particular date format if required.)

For URLs which have ended, you can then do:

=F2 < TODAY()

And for URLs ending in the next 7 days you will need something like:

=IF(F2<TODAY(), FALSE, IF(F2 - TODAY() > 7, FALSE, TRUE))