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

 

[SpillerBD]

You want the date value as a Text String?

 

[Rick Rothstein]

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]

First, if the Cell Formatting for that cell is Text, change it to General. Next, with the cell selected, bring up the Text To Columns dialog box (Data tab, Data Tools panel) and select Delimited when it appears, then click Next and make sure none of the Delimiters checkboxes are checked, then click the Finish button.

 

[SpillerBD]

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

What is displayed is the raw date/time value in your (systems default format? Rick?). If you change the value to a text string, you will loose all related date functions.
If you want the Cell to display the date/time as day/month/Year hh:mm, you need to change the CELLS formatting. THIS does not change the raw value you will see in the formula bar.
If you want

 

[suresh2017]

SpillerBD - Could you tell me how to change the CELLS formatting.

 

[Rick Rothstein]

SpillerBD - Could you tell me how to change the CELLS formatting.

First, select the cell, then click the drop down control on the Home tab in the Number panel... select the format for the cell from there. Alternately, you can press Ctrl+1 on the keyboard or you can right-click the cell and select "Format Cells..." from the popup menu that appears.

 

[Dave Patton]

Excel 2010
	A	B
1	Oct 31, 2017 11:18 PM	31/10/17 23:18
2
3	Custom formatted	dd/mm/yy hh:mm
4	Regional Settings dd/mm/yy
5	N.B. No trailing spaces
6
1e

Cell Formulas
Range	Formula
B1	=--(MID(A1,5,2)&LEFT(A1,3)&MID(A1,9,4))+TIMEVALUE(RIGHT(A1,8))

 

[Rick Rothstein]

Excel 2010
	A	B
1	Oct 31, 2017 11:18 PM	31/10/17 23:18
2
3	Custom formatted	dd/mm/yy hh:mm
4	Regional Settings dd/mm/yy
5	N.B. No trailing spaces
6
1e

Cell Formulas
Range	Formula
B1	=--(MID(A1,5,2)&LEFT(A1,3)&MID(A1,9,4))+TIMEVALUE(RIGHT(A1,8))

Assuming the OP wanted to do this by a formula, this would probably be more efficient...

=0+A1

 

[suresh2017]

Column A is having date - Oct 31, 2017 11:18 PM Column B formatted as Custom dd/mm/yyyy hh:mm then copied A value and pasted into column B still i am getting A format date only (Oct 31, 2017 11:18 PM) - I am sorry if am confusing more. i just wanted Column A date - Oct 31, 2017 11:18 PM need to display 31/10/2017 23:18 date format.

 

[suresh2017]

looks like this formula working how to get AM or PM also. yet to check all rows.