ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Value type (https://www.excelbanter.com/excel-discussion-misc-queries/57558-value-type.html)

sparx

Value type
 

How do I write a formula that can tell if a cell is a date or a value so
it can display the correct info - I hava 3 columns - one is text - one
is currency - one is date.
I have a cell that can pick between all 3 ( say A1 or B1 or C1 ) but it
shows the currency and date as a date style cell if its looking at the
currency value???


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775


Don Guillett

Value type
 
goto help index and type in IS

--
Don Guillett
SalesAid Software

"sparx" wrote in
message ...

How do I write a formula that can tell if a cell is a date or a value so
it can display the correct info - I hava 3 columns - one is text - one
is currency - one is date.
I have a cell that can pick between all 3 ( say A1 or B1 or C1 ) but it
shows the currency and date as a date style cell if its looking at the
currency value???


--
sparx
------------------------------------------------------------------------
sparx's Profile:
http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775




sparx

Value type
 

The IS command does not perform the function needed as there must be
another way of working it out.


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775


Peo Sjoblom

Value type
 
There is no way to tell if the cell value is a date (at least not full
proof)
an excel date is a number so you can test for ISNUMBER and ISTEXT thus the
IS functions are the way to go

=IF(ISNUMBER(A1),"do this","do that")
--

Regards,

Peo Sjoblom

"sparx" wrote in
message ...

The IS command does not perform the function needed as there must be
another way of working it out.


--
sparx
------------------------------------------------------------------------
sparx's Profile:

http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775




Don Guillett

Value type
 
put this into a regular modulethen =if(isadate(g4),1,2)

Function isadate(x)
If isdate(x) Then isadate = True
End Function

--
Don Guillett
SalesAid Software

"sparx" wrote in
message ...

The IS command does not perform the function needed as there must be
another way of working it out.


--
sparx
------------------------------------------------------------------------
sparx's Profile:
http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775




CLR

Value type
 
Please provide a couple of lines of sample data, the formula you use to do
the "picking" between the three columns, and the desired
results...............

Vaya con Dios,
Chuck, CABGx3



"sparx" wrote:


How do I write a formula that can tell if a cell is a date or a value so
it can display the correct info - I hava 3 columns - one is text - one
is currency - one is date.
I have a cell that can pick between all 3 ( say A1 or B1 or C1 ) but it
shows the currency and date as a date style cell if its looking at the
currency value???


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775



Dave Peterson

Value type
 
Maybe you could use something like:


sparx wrote:

How do I write a formula that can tell if a cell is a date or a value so
it can display the correct info - I hava 3 columns - one is text - one
is currency - one is date.
I have a cell that can pick between all 3 ( say A1 or B1 or C1 ) but it
shows the currency and date as a date style cell if its looking at the
currency value???

--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775


--

Dave Peterson

Dave Peterson

Value type
 
....something like...

=IF(AND(ISNUMBER(A1),LEFT(CELL("format",A1),1)="d" ),"it's a date","not a date")



sparx wrote:

How do I write a formula that can tell if a cell is a date or a value so
it can display the correct info - I hava 3 columns - one is text - one
is currency - one is date.
I have a cell that can pick between all 3 ( say A1 or B1 or C1 ) but it
shows the currency and date as a date style cell if its looking at the
currency value???

--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488775


--

Dave Peterson


All times are GMT +1. The time now is 08:37 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com