SOLVED

Cant understand why i cant apply formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1264274%22%20slang%3D%22en-US%22%3ECant%20understand%20why%20i%20cant%20apply%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264274%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%2C%20i'm%20hereby%20asking%20for%20help%20%3A%3C%2FP%3E%3CP%3EI'm%20trying%20to%20apply%20formula%20(%3C%2FP%3E%3CUL%3E%3CLI%3E%3CDIV%20class%3D%22preformatted%20panel%20conf-macro%20output-block%22%3E%3CDIV%20class%3D%22preformattedContent%20panelContent%22%3E%3CPRE%3E%3DTEXT(E2%2C%22mm%2Fdd%2Fyyyy%20hh%3Amm%22)%3C%2FPRE%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3Ebut%20its%20giving%20me%20this%20error%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Kwaker_0-1585571009600.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180499iC9811DAB39C02053%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Kwaker_0-1585571009600.png%22%20alt%3D%22Kwaker_0-1585571009600.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eand%20im%20not%20able%20to%20fix%20it%2C%20could%20you%20please%20find%20the%20solution%20please%3F%20thank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1264274%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264298%22%20slang%3D%22en-US%22%3ERe%3A%20Cant%20understand%20why%20i%20cant%20apply%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600835%22%20target%3D%22_blank%22%3E%40Kwaker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormula%20itself%20is%20correct%20if%20you%20are%20on%20English%20SKU.%20In%20other%20locale%20try%20to%20use%20semicolon%20instead%20of%20comma.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEven%20with%20correct%20formula%20it%20changes%20nothing%2C%20most%20probably%20in%20column%20E%20you%20have%20texts%2C%20not%20dates.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264321%22%20slang%3D%22en-US%22%3ERe%3A%20Cant%20understand%20why%20i%20cant%20apply%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264321%22%20slang%3D%22en-US%22%3E%3CP%3Ethanks%20for%20reply%2C%20originally%20it%20was%20text%20-%20i%20changed%20to%20custom%20type%20%3A%20but%20seems%20it%20didnt%20work%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3Eso%20i%20should%20convert%20to%20Numbers%20firts%20and%20than%20to%20custom%20type%3F%20thank%20you%20again%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Kwaker_0-1585571691493.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180500i57A1A3D544AE3A08%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Kwaker_0-1585571691493.png%22%20alt%3D%22Kwaker_0-1585571691493.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264312%22%20slang%3D%22en-US%22%3ERe%3A%20Cant%20understand%20why%20i%20cant%20apply%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264312%22%20slang%3D%22en-US%22%3E%3CP%3E%D0%B1%D0%BB%D0%B0%D0%B3%D0%BE%D0%B4%D0%B0%D1%80%D1%8E%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Good day, i'm hereby asking for help :

I'm trying to apply formula (

  • =TEXT(E2,"mm/dd/yyyy hh:mm")

but its giving me this error: 

Kwaker_0-1585571009600.png

and im not able to fix it, could you please find the solution please? thank you

 

10 Replies
Highlighted

@Kwaker 

Formula itself is correct if you are on English SKU. In other locale try to use semicolon instead of comma.

 

Even with correct formula it changes nothing, most probably in column E you have texts, not dates.

Highlighted

благодарю @Sergei Baklan 

Highlighted

thanks for reply, originally it was text - i changed to custom type : but seems it didnt work

so i should convert to Numbers firts and than to custom type? thank you again

 

Kwaker_0-1585571691493.png

 

@Sergei Baklan 

Highlighted

@Kwaker 

 

A sample file will go a long way to get help from many experts here

Highlighted

@Sergei Baklan semicolon worked, thank you so much:)

Kwaker_0-1585572285269.png

now the new issue -> instead of year it shows YYYY, do you know maybe how to fix that?

 

Highlighted
Best Response confirmed by Kwaker (Occasional Contributor)
Solution

@Kwaker 

Yes, only applying the format won't convert text to date. How to convert it depend what is your OS default format. As variant you may use Text to Columns - first split on date and time, when to date apply wizard with MDY format on third step, combine date and time back.

 

If that's your native format you may convert by adding zero, with Paste Special ->Add or by formula

Highlighted

@Kwaker 

And what is in formula bar for E2?

Highlighted
mm/dd/rrrr h:mm
Highlighted
i think the issue that im using CZ locale, its really pain
Highlighted

@Kwaker 

Most probably yes, data format you use doesn't meet your locale settings.