Nested function in excel formula

Question

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?

See stackoverflow.com/questions/23296538/…

MP24
– MP24

2014-05-23 15:01:13 +00:00
Commented May 23, 2014 at 15:01 — MP24
– MP24, Commented May 23, 2014 at 15:01

Rory · Accepted Answer · 2014-05-23 14:56:21Z

1

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.

edited May 23, 2014 at 14:56

answered May 23, 2014 at 14:49

Rory

34.2k5 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kallol Over a year ago

Thanks for your help. This approach is working fine in case all the cells G19:G31 has data but giving Value error in case any of the cells is empty. Can we handle that?

user1923975 Over a year ago

Have you tried using an Iferror formula around what was provided above?

Bathsheba · Accepted Answer · 2014-05-23 14:50:58Z

0

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.

edited May 23, 2014 at 14:50

answered May 23, 2014 at 14:39

Bathsheba

235k35 gold badges376 silver badges504 bronze badges

Collectives™ on Stack Overflow

Nested function in excel formula

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related