Convert Military time?

[REDX]

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.

 

[AgentSmith]

You mean 44 seconds? :wink:

Try this formula:

=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.

 

[REDX]

AgentSmith:

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

 

[Norie]

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))

 

[REDX]

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

 

[REDX]

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?

 

[Norie]

REDX

I thought we'd already dealt with this?

 

[REDX]

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

 

[REDX]

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?

 

[Norie]

REDX

How did you get the dates?

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