I would like to offer some kudos for the CEILING answer. I know this is an ancient thread but I thought I would pass on some performance testing.

I posed the following challenge to some Excel experts:

Suppose data dumped from a recordset into Excel resulted in Excel recording all dates and date-times, as numbers. Dates are whole numbers, DateTimes *might* be whole numbers, but generally are decimal numbers. And suppose testing the field type, all you got was a value of 7 - which means a general date. Your "mission" is to offer a formula I can use on the entire column of data so that if I get a TRUE from that formula, it tells me I need to give the column a DateTime numberformat, and if False, which indicates all whole numbers, I can then use simply a Date format.

I chose a formula that used the CEILING example offered in this thread. I got 4 other offered formulas. Here are all 5 formulas:


All of the formulas offered require array entry except the last one
=SUM(IFERROR(CEILING(A:A,1),0))SUM(A:A)
=SUM(N(MOD(IF(ISNUMBER(A:A),A:A,0),1)0))
=SUM(IFERROR(FIND(""."",A:A),0))0
=SUM(IFERROR(MOD(A:A,1),))0
=COUNT(INDEX(1/MOD(A:A,1),))

So on the worksheet, the last formula would likely be the fastest in terms of Excel's calculation engine. But consider that I was using VBA for this operation, and the EVALUATE method. That method
(1) assumes array-entered formula in its computation
(2) works on the true FULL column, whereas the formulas above, if entered on the worksheet, would be much faster because Excel's own calculation engine knows to ignore unused worksheet rows in the computation.


Here are the results in my benchmarking tests:

Remember, if the formulas resulted in TRUE, then the data was DateTime, and I did this on a range in col A that had a header and some dates, and there were non-whole numbers therefore DateTime is the proper output; and I ran each computation 100 times in a loop:




Result: DateTime Time taken: 49.593 seconds for 100 calculations using =SUM(N(MOD(IF(ISNUMBER(A:A),A:A,0),1)0))

Result: DateTime Time taken: 31.262 seconds for 100 calculations using =COUNT(INDEX(1/MOD(A:A,1),))

Result: DateTime Time taken: 52.058 seconds for 100 calculations using =SUM(IFERROR(FIND(".",A:A),0))0

Result: DateTime Time taken: 30.576 seconds for 100 calculations using =SUM(IFERROR(MOD(A:A,1),))0

Result: DateTime Time taken: 28.907 seconds for 100 calculations using =SUM(IFERROR(CEILING(A:A,1),0))SUM(A:A)

As this benchmarking showed, the method with CEILING was a little faster than all the other methods, even though it used TWO SUMMATIONS!!!

WAY TO GO!!