Date Format/Convert in Excel 2016

[suresh2017]

Hi All,

I am having a date Oct 31, 2017 11:18 PM (i am seeing in formula bar) i wanted to convert to [TABLE="width: 140"]
<tbody>[TR]
[TD="class: xl63, width: 140"]31/10/2017 23:18[/TD]
[/TR]
</tbody>[/TABLE]


Please help me

 

[admiral100]

Give that a try :

=MID(A1,5,FIND(",",A1)-5)&"/"&MONTH(DATEVALUE(LEFT(A1,3)& " 1"))&"/"&TRIM(MID(A1,FIND(",",A1)+1,6))&" "&TEXT((TRIM(RIGHT(A1,9))),"HH:MM")

 

[Dave Patton]

The length of the text string is 21
Try copying the information to a new spreadsheet.
Edit the cell reference as required and try =--(LEFT(A7,21))
Format the cell to your preference.

Excel 2010
	A	B	C
7	30/10/2017 2:48:11 PM	21	43038.61679
1ee

Cell Formulas
Range	Formula
B7	=LEN(A7)
C7	=--(LEFT(A7,21))

 

[Dave Patton]

You can download a tool to help you post a concise example to the forum.
The forum includes information on such tools.

One Addin for Excel is HtmlMaker.

see https://www.mrexcel.com/forum/about-board/1017050-mrexcel-html-maker-update-4.html

The latest version is one page 4 message 31.

 

[suresh2017]

Hi

Getting # Value error.

 

[Dave Patton]

You will have to provide clear information.
Cut and paste the data and formula that I provided.
Compare to your data.
Review formula results with Formula Evaluation.
You can provide a full description or use the Addin that I mentioned.

 

[suresh2017]

Hi Dave,

if you dont mind could you let me know how to send the excel file to you.

 

[Macropod]

if you dont mind could you let me know how to send the excel file to you.

Kindly don't ask people to let you send files to them (or vice-versa); that amounts to taking the discussion off-line, in violation of rule 4: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[suresh2017]

Sure will not do in future. add a feature we can upload files for more clarity on the questions. if it is already in wish list please ignore this message.

 

[Macropod]

This board has never supported that and there are no moves to do so. For alternatives see 'Posting Aids' in the Guidelines: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html