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
CodePudding user response:
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))