D
dyowee
Good day!
Is this possible to do programmatically?
Thanks.
Is this possible to do programmatically?
Thanks.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
Sub whatformat()
MsgBox (ActiveCell.NumberFormat)
End Sub
MsgBox (ActiveCell.NumberFormat)
End Sub
D
dyowee
This returns the actual number format, not the category, like 'General',
'Percentage', 'Currency', etc...
'Percentage', 'Currency', etc...
G
Gary''s Student
I don't think we can get it directly (because catagory is not a property of
the range object). However code can be written to figure it out.
For example, if the format has a % in it it must be Percent; if the format
has a $ in it it must be Currency; etc.
Need to determine some key characters and then do some kind of lookup.
Good Luck
the range object). However code can be written to figure it out.
For example, if the format has a % in it it must be Percent; if the format
has a $ in it it must be Currency; etc.
Need to determine some key characters and then do some kind of lookup.
Good Luck
D
Dave Peterson
Excel has its own
=cell("format",a1)
that will return a category (kind of).
Look at excel's help for =cell() and you'll find a list of those "Categories".
Then you can evaluate that formula and look at the resulting string (first
character or whole string???) to see what category you want.
This evaluates that formula:
Dim res As String
With Worksheets("sheet1")
res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
End With
MsgBox res
This is from xl2003's (USA version) help:
If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"
But the "G" used for both General and fraction seems pretty disconcerting!
=cell("format",a1)
that will return a category (kind of).
Look at excel's help for =cell() and you'll find a list of those "Categories".
Then you can evaluate that formula and look at the resulting string (first
character or whole string???) to see what category you want.
This evaluates that formula:
Dim res As String
With Worksheets("sheet1")
res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
End With
MsgBox res
This is from xl2003's (USA version) help:
If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"
But the "G" used for both General and fraction seems pretty disconcerting!
D
dyowee
Why does "12:00:00 AM" returns a "G"? =(
Dave Peterson said:Excel has its own
=cell("format",a1)
that will return a category (kind of).
Look at excel's help for =cell() and you'll find a list of those "Categories".
Then you can evaluate that formula and look at the resulting string (first
character or whole string???) to see what category you want.
This evaluates that formula:
Dim res As String
With Worksheets("sheet1")
res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
End With
MsgBox res
This is from xl2003's (USA version) help:
If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"
But the "G" used for both General and fraction seems pretty disconcerting!
Good day!
Is this possible to do programmatically?
Thanks.
N
Niek Otten
Maybe because there is text, not a "real" Excel date and time
You can easily check with the ISTEXT() function
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Why does "12:00:00 AM" returns a "G"? =(
|
| "Dave Peterson" wrote:
|
| > Excel has its own
| > =cell("format",a1)
| > that will return a category (kind of).
| >
| > Look at excel's help for =cell() and you'll find a list of those "Categories".
| >
| > Then you can evaluate that formula and look at the resulting string (first
| > character or whole string???) to see what category you want.
| >
| > This evaluates that formula:
| >
| > Dim res As String
| > With Worksheets("sheet1")
| > res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
| > End With
| > MsgBox res
| >
| > This is from xl2003's (USA version) help:
| >
| > If the Microsoft Excel format is CELL returns
| > General "G"
| > 0 "F0"
| > #,##0 ",0"
| > 0.00 "F2"
| > #,##0.00 ",2"
| > $#,##0_);($#,##0) "C0"
| > $#,##0_);[Red]($#,##0) "C0-"
| > $#,##0.00_);($#,##0.00) "C2"
| > $#,##0.00_);[Red]($#,##0.00) "C2-"
| > 0% "P0"
| > 0.00% "P2"
| > 0.00E+00 "S2"
| > # ?/? or # ??/?? "G"
| > m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
| > d-mmm-yy or dd-mmm-yy "D1"
| > d-mmm or dd-mmm "D2"
| > mmm-yy "D3"
| > mm/dd "D5"
| > h:mm AM/PM "D7"
| > h:mm:ss AM/PM "D6"
| > h:mm "D9"
| > h:mm:ss "D8"
| >
| > But the "G" used for both General and fraction seems pretty disconcerting!
| >
| >
| > dyowee wrote:
| > >
| > > Good day!
| > > Is this possible to do programmatically?
| > >
| > > Thanks.
| >
| > --
| >
| > Dave Peterson
| >
You can easily check with the ISTEXT() function
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| Why does "12:00:00 AM" returns a "G"? =(
|
| "Dave Peterson" wrote:
|
| > Excel has its own
| > =cell("format",a1)
| > that will return a category (kind of).
| >
| > Look at excel's help for =cell() and you'll find a list of those "Categories".
| >
| > Then you can evaluate that formula and look at the resulting string (first
| > character or whole string???) to see what category you want.
| >
| > This evaluates that formula:
| >
| > Dim res As String
| > With Worksheets("sheet1")
| > res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
| > End With
| > MsgBox res
| >
| > This is from xl2003's (USA version) help:
| >
| > If the Microsoft Excel format is CELL returns
| > General "G"
| > 0 "F0"
| > #,##0 ",0"
| > 0.00 "F2"
| > #,##0.00 ",2"
| > $#,##0_);($#,##0) "C0"
| > $#,##0_);[Red]($#,##0) "C0-"
| > $#,##0.00_);($#,##0.00) "C2"
| > $#,##0.00_);[Red]($#,##0.00) "C2-"
| > 0% "P0"
| > 0.00% "P2"
| > 0.00E+00 "S2"
| > # ?/? or # ??/?? "G"
| > m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
| > d-mmm-yy or dd-mmm-yy "D1"
| > d-mmm or dd-mmm "D2"
| > mmm-yy "D3"
| > mm/dd "D5"
| > h:mm AM/PM "D7"
| > h:mm:ss AM/PM "D6"
| > h:mm "D9"
| > h:mm:ss "D8"
| >
| > But the "G" used for both General and fraction seems pretty disconcerting!
| >
| >
| > dyowee wrote:
| > >
| > > Good day!
| > > Is this possible to do programmatically?
| > >
| > > Thanks.
| >
| > --
| >
| > Dave Peterson
| >
D
Dave Peterson
Just to add to Niek's response...
I entered 12:00:00 AM (as a real time) and got D9.
I entered 12:00:00 AM (as a real time) and got D9.
Why does "12:00:00 AM" returns a "G"? =(
Dave Peterson said:Excel has its own
=cell("format",a1)
that will return a category (kind of).
Look at excel's help for =cell() and you'll find a list of those "Categories".
Then you can evaluate that formula and look at the resulting string (first
character or whole string???) to see what category you want.
This evaluates that formula:
Dim res As String
With Worksheets("sheet1")
res = .Evaluate("cell(""Format""," & .Range("a1").Address & ")")
End With
MsgBox res
This is from xl2003's (USA version) help:
If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"
But the "G" used for both General and fraction seems pretty disconcerting!
Good day!
Is this possible to do programmatically?
Thanks.