Help in splitting a non stand excel date and time

%3CLINGO-SUB%20id%3D%22lingo-sub-2724222%22%20slang%3D%22en-US%22%3EHelp%20in%20splitting%20a%20non%20stand%20excel%20date%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2724222%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22177%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22177%22%3E11%20July%202014%2012%3A00%3A00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EI%20have%20a%20column%20with%20data%20an%20time%26nbsp%3B%20in%20the%20format%20above%20%2CI%20am%20finding%20it%20difficult%20to%20split%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20INT%20fuction%2C%20text%20to%20colum%20function%20%2Cconcatenate%20function%20still%20not%20getting%20the%20fix.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2724222%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2724384%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20in%20splitting%20a%20non%20stand%20excel%20date%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2724384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1147773%22%20target%3D%22_blank%22%3E%40Jagunjunior%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%20it%20is%20with%20the%20format%20%22dd%20mmmm%20yyyy%20hh%3Amm%3Ass%22%3C%2FP%3E%3CP%3EYou%20can%20either%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20the%20format%20of%20the%20cell%20(CTRL%2B1)%3C%2FP%3E%3CP%3Eor%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20a%20formula%20to%20meet%20your%20needs%20(Considering%20A2%20contains%20the%20date)%3C%2FP%3E%3CP%3E%3DTEXT(A2%2C%22mm%2Fdd%2Fyyyy%22)%26nbsp%3B%20Resulting%20in%20%2209%2F06%2F2021%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2724388%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20in%20splitting%20a%20non%20stand%20excel%20date%20and%20time%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2724388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1147773%22%20target%3D%22_blank%22%3E%40Jagunjunior%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20you%20have%20such%20a%20date%20in%20A2.%3C%2FP%3E%0A%3CP%3ETo%20extract%20the%20date%2C%20enter%20the%20formula%20%3DINT(1*A2)%20in%20a%20cell%20and%20format%20that%20cell%20as%20a%20date.%3C%2FP%3E%0A%3CP%3ETo%20extract%20the%20time%2C%20enter%20the%20formula%20%3DMOD(1*A2%2C1)%20in%20a%20cell%20and%20format%20that%20cell%20as%20a%20time.%3C%2FP%3E%0A%3CP%3EAlternatively%2C%20use%20%3DDATEVALUE(A2)%20and%20%3DTIMEVALUE(A2)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
11 July 2014 12:00:00

I have a column with data an time  in the format above ,I am finding it difficult to split

 

I have tried INT fuction, text to colum function ,concatenate function still not getting the fix.

 

Please help

7 Replies

@Jagunjunior 

I guess it is with the format "dd mmmm yyyy hh:mm:ss"

You can either 

Change the format of the cell (CTRL+1)

or 

Use a formula to meet your needs (Considering A2 contains the date)

=TEXT(A2,"mm/dd/yyyy")  Resulting in "09/06/2021"

=TEXT(A2,"h:mm:ss") 12:00:00

 

Remember that using formula you are converting the values to a text value.

So I would suggest you just make reference to the Date cell and apply the desired format.

@Jagunjunior 

Let's say you have such a date in A2.

To extract the date, enter the formula =INT(1*A2) in a cell and format that cell as a date.

To extract the time, enter the formula =MOD(1*A2,1) in a cell and format that cell as a time.

Alternatively, use =DATEVALUE(A2) and =TIMEVALUE(A2)

clt + 1 does not do anything used it before

CTRL+1 is a shortcut to access the Format Options.
There you can select or create your custom format for the cell you will apply to.

As I said, if you just want the date format only make reference to the cell where there is a full date format =A2 and apply your desired format using the format Option accessed by CTRL+1
Thank you so much ,it worked like a charm
Thank you for your help
Thank you for your help ,worked as well