Differentiate numbers and date in MSExcel
I am processing a large dataset. The data contains both numbers, String and Date. I want to differentiate between Date, String and Numbers. I am facing the problem of differentiating numbers and dates.
I am currently using the below formula.
=(IF(TYPE(A1)=2,A1,TEXT(A1,"mmmm d, yyyy"))&"& "&IF(TYPE(B1)=2,B1,TEXT(B1,"mmmm d, yyyy"))&"& "&IF(TYPE(C1)=2,C1,TEXT(C1,"mmmm d, yyyy")))
If I give 1, it formats it as Date January 01, 1900
Even I tried a function ISNUMBER
that returns a value of true for the date, for example ISNUMBER(10/01/1900)
.
source to share
ISNUMBER
doesn't work because dates are numbers in a real sense. It's just formatting that makes them appear as dates. For example, 00/01/1900
0 is formatted as a date. The same goes for the function TYPE
as it returns 1
for a number (no matter if the number is formatted as a number or a date)
Use this:
=LEFT(CELL("format",A1),1)
It returns d
if A1
formatted as date.
source to share