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]

Also for this Oct 9, 2017 2:31 AM date this formula is not working it is giving #VALUE ! error - Please help to correct this .

 

[Rick Rothstein]

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

If you use the drop down control on the Ribbon, then select "More Number Formats..." from its list (the other methods I mentioned in Message #6 take you to the same dialog box directly)... select Custom from the Category list and enter this in the Type field...

dd/mm/yyyy hh:mm AM/PM

By the way, in case you are not aware, you do not have to do this one cell at a time... you can select all of the cells you want to give the same format to first and then follow the procedure that I outlined above.

 

[suresh2017]

please ignore this request not required to get AM and PM as time is in 24 hours format. sorry for this post.

 

[suresh2017]

Dave ~ formula is working , but for this Oct 9, 2017 2:31 AM Date this formula is not working it is giving #VALUE ! error. i dont required AM/PM. - please help me.

 

[Dave Patton]

The string length is different.
Edit the formula to consider a single digit for date and a single digit for hours.

Excel 2010
	A	B
1	Oct 31, 2017 11:18 PM	31/10/17 23:18
2	Oct 9, 2017 2:31 AM	09/10/17 02:31
3
1e

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

 

[Rick Rothstein]

Dave ~ formula is working , but for this Oct 9, 2017 2:31 AM Date this formula is not working it is giving #VALUE ! error. i dont required AM/PM. - please help me.

The string length is different.
Edit the formula to consider a single digit for date and a single digit for hours.

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=--(MID(A1,5,2)&LEFT(A1,3)&MID(A1,9,4))+TIMEVALUE(RIGHT(A1,8))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=--(MID(A2,5,1)&LEFT(A2,3)&MID(A2,8,4))+TIMEVALUE(RIGHT(A2,7))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Just wondering if either of you have seen Message #8 yet (the required formula is much, much simpler than this)?

 

[Dave Patton]

Hi Rick
I did see your post.
Oct 31, 2017 would not coerce to an actual date.

The following 2 text strings will coerce to a date.

Excel 2010
	A	B
5	31Oct2017	31-Oct-17
6	31-Oct-2017	31-Oct-17
1e

Cell Formulas
Range	Formula
B5	=A5+0
B6	=A6+0

 

[Rick Rothstein]

Hi Rick
I did see your post.
Oct 31, 2017 would not coerce to an actual date.

It works fine for me. I wonder if this has something to do with the locale settings for dates on the computer. My locale setting for short dates is m/d/y... is your locale setting different?
[/QUOTE]

 

[Dave Patton]

I wonder if this has something to do with the locale settings for dates on the computer.

Yes definitely. That is why I mentioned Regional Settings in my first post.

 

[suresh2017]

Hi Dave,

Thanks for your help on date format. Sorry for asking again.

I have another date format time with seconds but don’t want to display seconds. Could you help me how to do this please.

	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

<tbody>
</tbody>