must be easy, trick

rplim2016

Board Regular
Joined
Jun 28, 2016
Messages
76
Hello Guys,



=IF(AND(CELL("format",A2)="D4",
CELL("format",B2)="D4",
CELL("format",C2)="D4",
CELL("format",D2)="D4"),
"is in DATE Format",)

above formula is to check if all values in A2 to D4 are in date.
my boss challenged me to simplify above formula.

please help me. :)
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Just tested again the Array Formula for you ...

https://www.mrexcel.com/forum/excel...ets-simple-count-post5247706.html#post5247706

And it does work ...

As an alternative ... a simple UDF can also perforrn the task ...

Code:
Function CountDates(rng As Range) As Long
Dim c As Range
Dim x As Long
  For Each c In rng
    If IsDate(c.Value) = True Then x = x + 1
  Next c
CountDates = x
End Function
P.S. For your info the Cell function does not accept an Array ...
 
Last edited:

rplim2016

Board Regular
Joined
Jun 28, 2016
Messages
76
Just tested again the Array Formula for you ...

https://www.mrexcel.com/forum/excel...ets-simple-count-post5247706.html#post5247706

And it does work ...

As an alternative ... a simple UDF can also perforrn the task ...

Code:
Function CountDates(rng As Range) As Long
Dim c As Range
Dim x As Long
  For Each c In rng
    If IsDate(c.Value) = True Then x = x + 1
  Next c
CountDates = x
End Function
P.S. For your info the Cell function does not accept an Array ...

Thanks a lot james!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,750
Messages
5,446,277
Members
405,393
Latest member
sully361

This Week's Hot Topics

Top