Nested function in excel formula

0
votes

I am trying to get the count of cells in a column containing dates having future date. The column values are in string format.

I have figured out the formula as :

=COUNTIF(G19:G31,">" & TODAY())

This function works when the column values are in date format. But its failing if the cell format is in string format.

I have figured out the formula to convert a cell string value to date :

=DATEVALUE(G19)

Here are my queries: 1) How to apply DATEVALUE on a range , in this case from G19:G31? 2) How to combine these two formulas in to one single formula?

2
excelexcel-formulavba
See stackoverflow.com/questions/23296538/… – MP24

2 Answers

1
votes

You could use:

=SUMPRODUCT(--((G19:G31+0)>TODAY()))

COUNTIF will not accept anything other than a range as its first argument so you can't use functions there.

0
votes

An alternative is to write =SUM(IF(DATEVALUE(G19:G31) > TODAY(), 1, 0))

But this will be an array formula: you need to press Ctrl + Shift + Return once you've done editing, rather than just Return.