SOLVED

Diferenciar formatos de fecha en funcion CELDA()

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3168230%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Bes-ES%5C%26quot%3B%22%3EDifferentiate%20date%20formats%20in%20CELL()%20function%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3168230%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Bes-ES%5C%26quot%3B%22%3E%3CP%3EThe%20CELL(%22format%22%3B%20A1)%20returns%20a%20code%20that%20begins%20with%20%22D%22%20and%20a%20number%20for%20variants.%20But%20if%20the%20A1%20cell%20format%20is%20%22long%20date%22%2C%20it%20returns%20a%20%22G%22%20code.%3CBR%20%2F%3E%20%22G%22%20is%20the%20same%20code%20as%20numerics%2C%20so%20I%20can't%20detect%20cell%20formatting%20when%20it's%20date%20in%20long%20date%20format.%3CBR%20%2F%3E%20DO%20YOU%20HAVE%20A%20SOLUTION%20FOR%20THE%20ISSUE...%3F%3CBR%20%2F%3E%20Is%20it%20possible%20to%20include%20new%20formats%20in%20some%20way%20or%20to%20be%20able%20to%20discriminate%20if%20it%20is%20numeric%20or%20long%20date%3F%3CBR%20%2F%3E%20Thanks%20now%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3168230%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Bes-ES%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3168230%22%20slang%3D%22es-ES%22%3EDifferentiate%20date%20formats%20in%20CELL()%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3168230%22%20slang%3D%22es-ES%22%3E%3CP%3EThe%20CELL(%22format%22%3B%20A1)%20returns%20a%20code%20that%20begins%20with%20%22D%22%20and%20a%20number%20for%20variants.%20But%20if%20the%20A1%20cell%20format%20is%20%22long%20date%22%2C%20it%20returns%20a%20%22G%22%20code.%3CBR%20%2F%3E%20%22G%22%20is%20the%20same%20code%20as%20numerics%2C%20so%20I%20can't%20detect%20cell%20formatting%20when%20it's%20date%20in%20long%20date%20format.%3CBR%20%2F%3E%20DO%20YOU%20HAVE%20A%20SOLUTION%20FOR%20THE%20ISSUE...%3F%3CBR%20%2F%3E%20Is%20it%20possible%20to%20include%20new%20formats%20in%20some%20way%20or%20to%20be%20able%20to%20discriminate%20if%20it%20is%20numeric%20or%20long%20date%3F%3CBR%20%2F%3E%20Thanks%20now%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3168230%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

La funcion CELDA("formato";A1) devuelve un código que comienza con "D" y un numero para las variantes. Pero si el formato de celda A1 es "fecha larga", devuelve un código "G".
"G" es el mismo código de los numéricos, por lo que no puedo detectar el formato de la celda cuando es fecha en formato fecha larga.
¿TIENEN UNA SOLUCIÓN PARA EL TEMA...?
¿es posible incluir formatos nuevos de alguna forma o poder discriminar si es numérico o es fecha long?
Gracias desde ya

 

2 Replies
best response confirmed by UnTalLeo (New Contributor)
Solution

@UnTalLeo 

La función CELDA con el argumento "formato" reconoce solo un número limitado de formatos de fecha y no tiene reconocimiento internacional. Podrías usar una función de VBA:

Function Formato(rng As Range) As String
    Formato = rng.NumberFormatLocal
End Function

Usar así:

=Formato(A1)
Muchas gracias. Es lo que necesitaba.