Determining the format of a cell

Bengt

Active Member
Joined
Mar 4, 2008
Messages
267
Probably a simple question: I would like to find out if a certain cell was formatted as "Date", and I thought I could use the IsDate-function, but obviously it returns true if the contents of the cell can be converted to a date, regardless of how it is formatted. Is there a way to ask (from VBA) if a cell is formatted as date?

Bengt
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
There is the formula =CELL("format",A1) which might be of help. See the formula help in order to understand the result.
 
Upvote 0
Upvote 0
To follow up on Armando's message... do you want to know if the cell is formatted as a "Date" (what you wrote you wanted in your original posting) or that it actually contains a (valid) date value? I ask this because you can format a cell as "Date" but put just normal text in it (say, your name for example) and Excel will do that without complaining.
I just saw your response to Armando. Using what Jubjab posted as a model, here is VB code (what I think you want) that will tell you whether a cell is formatted as a date or not...
Code:
CellAddr = "G5"
If Evaluate("CELL(""format""," & CellAddr & ")") Like "D*" Then
  MsgBox "Cell " & CellAddr & " is formatted as a date"
Else
  MsgBox "Cell " & CellAddr & " is not formatted as a date"
End If
 
Upvote 0
To follow up on Armando's message... do you want to know if the cell is formatted as a "Date" (what you wrote you wanted in your original posting) or that it actually contains a (valid) date value? I ask this because you can format a cell as "Date" but put just normal text in it (say, your name for example) and Excel will do that without complaining.
The reason for my original question was that I have a cell that contains the value "1A" and that value happens to be the name of a school class. In my VBA-code, I go through the sheet in question and there is a code line that tries to identify "real" dates, like 2 apr 2013 or similar, and then write it to a textfil with format(A1,"yyyy-mm-dd"). When my code hits the cell with "A1" in it, it converts it to 1899-12-30, because IsDate("1A") has returned true. I am searching for a way to distinguish between "real" dates and not so real dates, and one attempt was to format the cells that actually contains dates as "Date" and have the general format on other cells.
 
Upvote 0
I just saw your response to Armando. Using what Jubjab posted as a model, here is VB code (what I think you want) that will tell you whether a cell is formatted as a date or not...
Code:
CellAddr = "G5"
If Evaluate("CELL(""format""," & CellAddr & ")") Like "D*" Then
  MsgBox "Cell " & CellAddr & " is formatted as a date"
Else
  MsgBox "Cell " & CellAddr & " is not formatted as a date"
End If

Thanks for your code. However, I couldn't get it to recognize a date formatted cell though. However I tried to format the cell, it always returned the result that the cell in question was not formatted as a date, even though I explicitly said so.
 
Upvote 0
Thanks for your code. However, I couldn't get it to recognize a date formatted cell though. However I tried to format the cell, it always returned the result that the cell in question was not formatted as a date, even though I explicitly said so.

The problem is that you are using a custom date formatting, so the formula evaluates to G.
 
Upvote 0
Thanks for your code. However, I couldn't get it to recognize a date formatted cell though. However I tried to format the cell, it always returned the result that the cell in question was not formatted as a date, even though I explicitly said so.
It worked for me in several tests I did before I posted it. Tell me what date format you used that it did not recognize (remember, we are talking about the cell format applied to the cell, not the contents of the cell itself).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top