Format issue

%3CLINGO-SUB%20id%3D%22lingo-sub-2631298%22%20slang%3D%22en-US%22%3EFormat%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631298%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20attached%20the%20excel%20sheet%20wherein%20I'm%20facing%20issues%20with%20formatting%20of%20date%20and%20number%20in%20a%20single%20go.%20I%20have%20converted%20text%20to%20date%20format%20(US)%20for%20column%20B%20but%20it's%20not%20working.%20Again%20I've%20inserted%20formula%20for%20column%20B%20to%20convert%20it%20into%20%22mm%2Fdd%2Fyyyy%22%20which%20is%20also%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20I%20am%20facing%20similar%20issue%20with%20column%20D%20and%20column%20E%20as%20their%20not%20converting%20into%20numbers%20even%20though%20I%20have%20changed%20the%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20filter%20the%20date%20by%20month%20and%20year%20which%20is%20not%20happening%20as%20of%20now.%20And%20I%20want%20to%20sum%20the%20numbers%20which%20is%20not%20functioning%20now.%20It%20would%20be%20great%20help%20if%20someone%20can%20fix%20this%20and%20show%20me%20the%20steps%20to%20resolve%20it%20on%20my%20own%20in%20future.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2631298%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631364%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631364%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F577386%22%20target%3D%22_blank%22%3E%40Dharmendra_Bharwad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fformat-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EFormatieren%20eines%20Datums%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EWhen%20you%20type%20something%20like%20%3CSTRONG%3E2%2F2%3C%2FSTRONG%3E%20in%20a%20cell%2C%20Excel%20for%20the%20web%20thinks%20you%E2%80%99re%20typing%20a%20date%20and%20shows%20it%20as%20%3CSTRONG%3E2-Feb%3C%2FSTRONG%3E.%20But%20you%20can%20change%20the%20date%20to%20be%20shorter%20or%20longer.%3C%2FP%3E%3CP%3ETo%20see%20a%20short%20date%20like%202%2F2%2F2013%2C%20select%20the%20cell%2C%20and%20then%20click%20%3CSTRONG%3EHome%3C%2FSTRONG%3E%20%26gt%3B%20%3CSTRONG%3ENumber%20Format%3C%2FSTRONG%3E%20%26gt%3B%20%3CSTRONG%3EShort%20Date%3C%2FSTRONG%3E.%20For%20a%20longer%20date%20like%20Saturday%2C%20February%2002%2C%202013%2C%20pick%20%3CSTRONG%3ELong%20Date%3C%2FSTRONG%3E%20instead.%3C%2FP%3E%3CP%3E%3CSTRONG%3EClick%20on%20the%20Link%20to%20see%20Tipps.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EAdditional%20Informations%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fformat-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EFormat%20numbers%20as%20dates%20or%20times%3C%2FA%3E%3C%2FP%3E%3CP%3EYou%20can%20also%20press%20%3CSTRONG%3ECTRL%2B1%3C%2FSTRONG%3E%20to%20open%20the%20%3CSTRONG%3EFormat%20Cells%3C%2FSTRONG%3E%20dialog%20box.%3C%2FP%3E%3CP%3EMore%20informations%20in%20the%20Link.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

I have attached the excel sheet wherein I'm facing issues with formatting of date and number in a single go. I have converted text to date format (US) for column B but it's not working. Again I've inserted formula for column B to convert it into "mm/dd/yyyy" which is also not working.

 

Now, I am facing similar issue with column D and column E as their not converting into numbers even though I have changed the format.

 

I want to filter the date by month and year which is not happening as of now. And I want to sum the numbers which is not functioning now. It would be great help if someone can fix this and show me the steps to resolve it on my own in future.

 

Thanks.

4 Replies

@Dharmendra_Bharwad 

 

Formatieren eines Datums

When you type something like 2/2 in a cell, Excel for the web thinks you’re typing a date and shows it as 2-Feb. But you can change the date to be shorter or longer.

To see a short date like 2/2/2013, select the cell, and then click Home > Number Format > Short Date. For a longer date like Saturday, February 02, 2013, pick Long Date instead.

Click on the Link to see Tipps.

 

Additional Informations:

Format numbers as dates or times

You can also press CTRL+1 to open the Format Cells dialog box.

More informations in the Link.

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Hi @NikolinoDE ,

 

I am very well aware about the short cut to format via Ctl+1 and date formatting. However, still I am facing the issues even after formatting it as a DATE. I want to filter the date for a particular period which I am unable to do as of now. If you can resolve it, it would be great help.

 

P.S.: I have found the solution after tricking few things. Thanks for your help anyway.

Excuse me, but I don't understand what I mean by "I want to filter the date for a particular period".
Please describe your project step by step and from which cell to which cell or in which cell / column the problem is with you.

Thank you for your patience and time.

Nikolino
I know I don't know anything (Socrates)

@Dharmendra_Bharwad Column B contained texts that look like dates and in column C you created TEXT formula. This doesn't change it into a date. Use text to column (Data ribbon). In step 3, make sure you select DMY as date format. Have done that for you.

 

With regard to columns C and D, these numbers aren't real numbers. They are merely texts looking like numbers. Use Find & Replace (Ctrl-H) to remove the commas. Find: , Replace: leave empty. Have done that as for you.

 

I guess this will enable you to filter by date and sum the numbers.

 

See attached.