Date + Military Time conversion

[Dandada2602]

Hi,

I need to convert the following format to excel date and time format. YY-MMM-DD + military time to excel.[TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115"]18-Oct-27 0300[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 115"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD]18-Oct-29 0301[/TD]
[/TR]
[TR]
[TD]18-Oct-28 0925[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks

 

[taigovinda]

Hi,

I had typed up a whole response about how Excel stores dates as integers and you just need to format the cells to display the way you want... then I read your question more carefully!

This has a formula (probably there's a more efficient way but this works as long as your format is always the exact same) and 3 ways to format the result:

	A	B	C	D
18-Oct-29 0301
18-Oct-28 0925
18-Nov-22 1925

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]10/29/2018[/TD]
[TD="align: right"]10/29/2018 03:01[/TD]
[TD="align: right"]43,402.13[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]10/28/2018[/TD]
[TD="align: right"]10/28/2018 09:25[/TD]
[TD="align: right"]43,401.39[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]11/22/2018[/TD]
[TD="align: right"]11/22/2018 19:25[/TD]
[TD="align: right"]43,426.81[/TD]

</tbody>

Sheet3​

[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,4,3)&" "&MID(A1,8,2)&", 20"&LEFT(A1,2)&" "&LEFT(RIGHT(A1,4),2)&":"&RIGHT(A1,2))+0[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=B1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D1[/TH]
[TD="align: left"]=C1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Tai

 

[Dandada2602]

That did it! Thanks!