How To: Use Cell("Format") to return MMMM YYYY instead of D3

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I'm trying to get the format property of a of a cell with using macros just using worksheet functions. When I use Cell("Format",A1) it returns D3, but I need the MMMM YYYY custom format that I have placed on the cell to be returned, any ideas how to get it

TI
KM
 
Hi
AFAIK this is not possible with using only worksheet
functions
-----Original Message-----
Hi,
I'm trying to get the format property of a of a cell
Click to expand...
with using macros just using worksheet functions. When I
use Cell("Format",A1) it returns D3, but I need the MMMM
YYYY custom format that I have placed on the cell to be
returned, any ideas how to get it?
 
What does AFAIK mea

----- Frank Kabel wrote: ----

H
AFAIK this is not possible with using only worksheet
function
-----Original Message----
Hi
I'm trying to get the format property of a of a cell
Click to expand...
with using macros just using worksheet functions. When I
use Cell("Format",A1) it returns D3, but I need the MMMM
YYYY custom format that I have placed on the cell to be
returned, any ideas how to get it
 
Grüezi Kevin

Kevin McCartney schrieb am 18.05.2004
I'm trying to get the format property of a of a cell with using macros
just using worksheet functions. When I use Cell("Format",A1) it returns
D3, but I need the MMMM YYYY custom format that I have placed on the
cell to be returned, any ideas how to get it?
Click to expand...

There might be a possibility when you use xl4Macros in a defined name.

Insert --> Names --> Define --> Names in Workbook: 'Cellformat' --> Refers
to:

=GET.CELL(7;INDIRECT("RC(-1)";FALSE))

--> [Add] --> [OK]

When you like to see the format of A1, then:

B1 =Cellformat

'Cellformat' evaluates the cell left of where you use it in the table.

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]
 
Hi
AFAIK: As far as I know
-----Original Message-----
What does AFAIK mean

----- Frank Kabel wrote: -----

Hi
AFAIK this is not possible with using only worksheet
functions
cell
with using macros just using worksheet functions. When I
use Cell("Format",A1) it returns D3, but I need the MMMM
YYYY custom format that I have placed on the cell to be
returned, any ideas how to get it?
.
Click to expand...
 
it might be important to note that if you copy a cell using this formula and
paste it on another sheet, in xl2000 and earlier, this will cause a general
protection fault, excel will close and you will loose any changes to your
workbook.

I believe xl2002 and possibly xl2003 are more tolerant.
--
Regards,
Tom Ogilvy

Thomas Ramel said:
Grüezi Kevin

Kevin McCartney schrieb am 18.05.2004
I'm trying to get the format property of a of a cell with using macros
just using worksheet functions. When I use Cell("Format",A1) it returns
D3, but I need the MMMM YYYY custom format that I have placed on the
cell to be returned, any ideas how to get it?
Click to expand...

There might be a possibility when you use xl4Macros in a defined name.

Insert --> Names --> Define --> Names in Workbook: 'Cellformat' --> Refers
to:

=GET.CELL(7;INDIRECT("RC(-1)";FALSE))

--> [Add] --> [OK]

When you like to see the format of A1, then:

B1 =Cellformat

'Cellformat' evaluates the cell left of where you use it in the table.

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]
Click to expand...
 
Grüezi Tom

Tom Ogilvy schrieb am 18.05.2004
it might be important to note that if you copy a cell using this formula and
paste it on another sheet, in xl2000 and earlier, this will cause a general
protection fault, excel will close and you will loose any changes to your
workbook.
Click to expand...

Thanks for upgrading my knowledge :-)
I believe xl2002 and possibly xl2003 are more tolerant.
Click to expand...

It may be that SP3 fixed this problem - Excel refuses to paste this
'macro-formula', but it stays alive.

--
Regards

Thomas Ramel
- MVP for Microsoft-Excel -

[Win XP Pro SP-1 / xl2000 SP-3]
 
Hi Kevin,
For dates and time you may not necessarily get what you ask for
because Excel may take your format and say that is Kevn's regional
format. But see if this gets what you want -- it definitely will not
give you D3.
See my http://www.mvps.org/dmcritchie/excel/formula.htm#getformat

Function GetFormat(Cell as Range) as String
GetFormat = cell.NumberFormat
End Function

=GetFormat(A3)
=Personal.xls!GetFormat(A3)-


Kevin McCartney said:
Hi,
I'm trying to get the format property of a of a cell with using macros just using worksheet functions. When I use
Click to expand...
Cell("Format",A1) it returns D3, but I need the MMMM YYYY custom format that I have placed on the cell to be returned, any ideas how
to get it?
 
For dates and time you may not necessarily get what you ask for
because Excel may take your format and say that is Kevn's regional
format. But see if this gets what you want -- it definitely will not
give you D3.
See my http://www.mvps.org/dmcritchie/excel/formula.htm#getformat

Function GetFormat(Cell as Range) as String
GetFormat = cell.NumberFormat
End Function ...
Click to expand...

In other words, there's no way to do this without using some form of macro
programming, either XLM formulas in defined names (which can be dangerous in
older XL versions) or VBA.

If the OP can't use VBA or XLM, the OP can't get the number format.
 
Back
Top