SOLVED
Home

Problemas con el reconocimiento de un formato personalizado en celda

%3CLINGO-SUB%20id%3D%22lingo-sub-893390%22%20slang%3D%22es-ES%22%3EProblems%20with%20recognition%20of%20a%20custom%20format%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893390%22%20slang%3D%22es-ES%22%3E%3CP%3EHello%20good%20morning%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20scenario%20is%20%3CEM%3Eas%20follows%3A%20after%20migrating%20to%20Office%20365%2C%20we%20had%20an%20excel%20file%20with%20multiple%20queries%20connected%20to%20an%20Oracle%20BD.%20Each%20query%20downloads%20the%20data%20into%20excel%20sheet%20tables%20with%20predefined%20formats%2C%20for%20example%2C%20for%20a%20date%20type%20field%2C%20we%20formatted%20the%20column%20with%20a%20custom%20field%20of%20type%3A%20''dd%2Fmm%2Fyyyy%20h%3Amm'.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EPre-migration%20of%20the%20Office%20suite%20happens%2C%20our%20data%20took%20the%20correct%20format%20once%20the%20data%20upload%20was%20completed%20on%20the%20table.%20Now%20the%20format%20is%20lost%20-%20it%20takes%20the%20General%20format%20-%20and%20the%20date%20field%20we%20see%20it%20numerically%2C%20for%20example%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20683px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135428i0A88369E085F18F0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22SituationActual.png%22%20title%3D%22SituacionActual.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EWe%20tried%20to%20modify%20the%20custom%20cell%20format%2C%20for%20example%20to%20''dd%2Fmm%2Fyyyy%20h%3Amm%3Ass'%20or%20''dd%2Fmm%2Fyyyy%20hh%3Amm%3Ass'%20without%20being%20this%20successful.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EI%20appreciate%20in%20advance%20any%20suggestions.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EBest%20regards.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-893390%22%20slang%3D%22es-ES%22%3E%3CLINGO-LABEL%3EExcel%20Problem%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20Query%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893422%22%20slang%3D%22en-US%22%3ERe%3A%20Problemas%20con%20el%20reconocimiento%20de%20un%20formato%20personalizado%20en%20celda%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893422%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F420079%22%20target%3D%22_blank%22%3E%40adrian_dsr%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20the%20following%3A%3C%2FP%3E%3COL%3E%3CLI%3ESelect%20the%20column%20that%20you%20wish%20to%20update%20the%20format.%3C%2FLI%3E%3CLI%3EUnder%20data%20tab%2C%20%22Text%20to%20Columns%22%3C%2FLI%3E%3CLI%3E%22Delimited%22%2C%20Next%3C%2FLI%3E%3CLI%3EUncheck%20all%20Delimiters%2C%20Finish%3C%2FLI%3E%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-896266%22%20slang%3D%22es-ES%22%3ERe%3A%20Problems%20with%20recognizing%20a%20custom%20format%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-896266%22%20slang%3D%22es-ES%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%20%2C%20thanks%20for%20your%20reply%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20About%20your%20suggestion%2C%20I%20cant%20apply%20it%20because%20previously%20I%20had%20no%20data%20on%20my%20cells%2C%20let%20me%20explain%20it%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3E%26nbsp%3BI%20have%20a%20table%20with%20all%20my%20columns%20formatted%2C%20the%20one%20with%20the%20problem%20has%20a%20custom%20format%20like%20'dd%2Fmm%2Fyyyy%20h%3Amm'%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20327px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135641i51672FA05584EB38%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22F2.jpg%22%20title%3D%22F2.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3EAfter%20we%20received%20data%20from%20our%20Oracle%20DB%20(we%20import%20data%20using%20powerquery)%20and%20loaded%20it%20to%20the%20table%2C%20we%20miss%20the%20format%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20275px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135643iA0DE1859335B5C8D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22F3.jpg%22%20title%3D%22F3.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3BWe%20used%20this%20%22template%22%20before%20to%20update%20the%20Office%20suite%20to%20%22Office%20365%22%20and%20we%20had%20no%20problem%2C%20%3CSPAN%3Ethe%20custom%20format%20was%20maintained.%20We%20were%20wondering%20if%20it%20could%20be%20related%20to%20the%20new%20version%20of%20Excel%20...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20in%20advance.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-918630%22%20slang%3D%22es-ES%22%3ERe%3A%20Problems%20with%20recognizing%20a%20custom%20format%20in%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-918630%22%20slang%3D%22es-ES%22%3E%3CP%3EThere%20is%20an%20option%20on%20the%20Data%20tab%2C%20%22Properties%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOpens%20a%20form%20named%20%22External%20Data%20Properties%22.%20Also%20checking%20the%20%22Keep%20order%2C%20filter%2C%20and%20column%20layout%22%20option%20solves%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
adrian_dsr
New Contributor

Hola buen día,

 

El escenario es el siguiente: previa migración a Office 365, teníamos un archivo excel con varias consultas conectadas a una BD Oracle. Cada consulta descarga los datos en tablas de hoja excel con formatos ya predefinidos, por ejemplo, para un campo de tipo fecha, hemos formateado la columna con un campo personalizado de tipo: ''dd/mm/aaaa h:mm".

 

Pasa que previo a la migración de la suite de Office, nuestros datos tomaban el formato correcto una vez se completaba la carga de datos sobre la tabla. Ahora el formato se pierde -toma el formato General- y el campo de la fecha lo vemos númerico, por ejemplo:

SituacionActual.png

 

Hemos intentado modificar el formato personalizado de la celda, por ejemplo a ''dd/mm/aaaa h:mm:ss" o ''dd/mm/aaaa hh:mm:ss" sin ser esto exitoso.

 

Agradezco de antemano cualquier sugerencia.

 

Saludos.

3 Replies
Highlighted

Hello @adrian_dsr,

 

Try the following:

  1. Select the column that you wish to update the format.
  2. Under data tab, "Text to Columns"
  3. "Delimited", Next
  4. Uncheck all Delimiters, Finish
Highlighted

Hello @PReagan  , thanks for your reply

 

  About your suggestion, I cant apply it because previously I had no data on my cells, let me explain it:

 

  1.  I have a table with all my columns formatted, the one with the problem has a custom format like 'dd/mm/aaaa h:mm':
    F2.jpg
  2. After we received data from our Oracle DB (we import data using powerquery) and loaded it to the table, we miss the format:
    F3.jpg

 

 We used this "template" before to update the Office suite to "Office 365" and we had no problem, the custom format was maintained. We was wondering if it could be related to the new version of Excel ...

 

Thanks in advance.

Highlighted
Solution

Existe una opción en la pestaña datos, "Propiedades".

 

Abre un formulario con nombre "Propiedades de los datos externos". Marcando también la opción "Mantener orden, filtro y diseño de columna" solventa el problema.

 

Saludos.