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

 

[suresh2017]

Hi Dave,

Thanks for your help on date format. Sorry for asking again. Corrected my request.

I have another date format time with seconds (A) but don’t want to display seconds. It should display Format (DD/MM/YYYY HH:MM – (Time 24 Hours Format)). Could you help me how to do this please.

	A	B – Format (DD/MM/YYYY HH:MM – (Time 24 Hours Format))
1	09/10/2017 10:31:02 AM	09/10/2017 10:31
2	08/10/2017 9:35:01 AM	08/10/2017 9:35

<tbody>
</tbody>

 

[Dave Patton]

Excel 2010
	A	B
1	09/10/2017 10:31:02 AM	09/10/2017 10:31
2	08/10/2017 9:35:01 AM	08/10/2017 9:35
3	18/10/2017 10:35:01 PM	18/10/2017 22:35
4
5	You could coerce column A to a number and custom format.
6	Use custom format see Column B
7	Custom format	dd/mm/yyyy h:mm
1ee

Cell Formulas
Range	Formula
B1	=A1+0

Many people would prefer the following format 9-Oct-2017 10:31

 

[suresh2017]

Hi Dave ,

Sorry to ask , Coerce column A to a number ? How to do this ?

 

[Dave Patton]

B1 coerces A1 to a value by Adding 0

To coerce the text in Column A to Values

Put 0 in a blank cell
Copy the cell Ctrl+C
Select the range to coerce
Paste Paste Special Add

 

[suresh2017]

Hi Deve,

Put 0 in a blank cell
Copy the cell Ctrl+C
Select the range to coerce (selected Column A)
Paste Paste Special Add

after that i have cheeked =ISTEXT(A) = result is TRUE , so i am assuming columan A is not converted to number. am i following your instructions correct ?

 

[Dave Patton]

Initial information

Excel 2010
	A
4	09/10/2017 10:31:02 AM
5	08/10/2017 9:35:01 AM
6	18/10/2017 10:35:01 PM
1ee

After adding 0

Excel 2010
	A
4	43017.43822
5	43016.39932
6	43026.94098
1ee

After format

Excel 2010
	A
4	09/10/2017 10:31
5	08/10/2017 9:35
6	18/10/2017 22:35
1ee

What exactly is in Column A?
Does the data including include leading or trailing spaces?

 

[suresh2017]

Hi Dave

Thanks for your mail.

is there any way i can send you my excel 2016 file with dates?

I have tried =TRIM (A) to remove leading or trailing spaces and after that adding 0 with below steps

Put 0 in a blank cell
Copy the cell Ctrl+C
Select the range to coerce (selected Column A)
Paste Paste Special Add

but still it is not converting as number like what you displayed in your response second image like below.


43017.43822
43016.39932
43026.94098


****** id="cke_pastebin" style="position: absolute; top: 184px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">A443017.43822543016.39932643026.94098</body>

 

[Dave Patton]

try the following

Excel 2010
	A	B	C
4	09/10/2017 10:31:02 AM	22	43017.43822
1ee

Cell Formulas
Range	Formula
B4	=LEN(A4)
C4	=--(LEFT(A4,22))

If the string length is not the same as the visible characters, determine if the invisible character is at the front or end of the string.
Try a formula similar to C4. N.B. The double negative (--) coerces the text to a number.
Remove the invisible character.

 

[Dave Patton]

Edit of above

If the string length is not the same as the visible characters, determine if the invisible character is at the front or end of the string.
With my example. If the string length was 23 and the invisible character was at the end,
the formula in C4 would work.Try a formula similar to C4. N.B. The double negative (--) coerces the text to a number.Remove the invisible character.

 

[suresh2017]

Hi Dave,

Thanks for your mail.

for testing i have tried for Length value passing in "C" 22 to 24 , bust still i ma getting # VALUE eorror.


A B C
1 Date As Text String Length - '=LEN(A1) (--) coerces the text to a number '=--(LEFT(A1,23))
30/10/2017 2:48:11 PM 23 #VALUE !