Convert Military time?

REDX

New Member
Trying to write an expression that converts military time to normal time.

An example would be 142344: 14H/23M/44seconds.

I need it to say 2:23 P.M. and 44 seconds.

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
You mean 44 seconds? :wink:

Try this formula:
Code:
``=TIME(VALUE(LEFT(A1,2)),VALUE(MID(A1,3,2)),VALUE(RIGHT(A1,2)))``
You should then be able to format the cell as required.

AgentSmith:

Thanks...I edited my post. I'll try your expression and let you know

REDX

Is this Access or Excel?

AgentSmith's formula is for Excel though you could use a similar expresssion in Access.

TimeSerial(Left([TextTime],2),Mid([TextTime],3,2),Right([TextTime],2))

Good catch Norie! It was for access. The formula worked perfectly...

How do I convert my text dates, example: '10/25/2005 to a real date? I import my TXT into access that looks like this 20051025 and using the DateSerial expression it comes out as text through my query, ('10/25/2005).

I need to have real dates that I can add and subtract days too. Am I on the right track by using the expression VAL?

REDX

I thought we'd already dealt with this?

My mistake, I saw the (') in front of my dates and assumed they were still text. I ran everything through and they seem to add and subtract fine but was told by a c-o-worker of mine that they were no good?

He wants to format them as MMMMM-DD-YYYY and they wont format? I'm happy the way they are.

I'll keep you poated

After much exhaustion this morning...

It seems that my converted TXT dates now look like real dates but will not sort correctly, they sort as if they are still text?

REDX

How did you get the dates?

What type of field are the 'real' dates in?

Replies
3
Views
227
Replies
2
Views
307
Replies
0
Views
592
Replies
15
Views
412
Replies
7
Views
97

1,203,485
Messages
6,055,685
Members
444,807
Latest member
RustyExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back