Reformat Time

V

Vel

I need to reformat a time in an import procedure.

The time in the original spreadsheet is formatted in military time, but
without decimals or colons.

So, I need to change

600 to 6:00
1300 to 13:00

etc.
 
V

Vel

Here's what I came up with:

TimeOut: Format(Int([F6]/100) & ":" & Right([F6],2),"Short Time")

Is there anything obviously unnecessary or redundant there? Or is there a
better way to do it?
 
K

KARL DEWEY

Format([F6],"00\:00")

Vel said:
Here's what I came up with:

TimeOut: Format(Int([F6]/100) & ":" & Right([F6],2),"Short Time")

Is there anything obviously unnecessary or redundant there? Or is there a
better way to do it?




Vel said:
I need to reformat a time in an import procedure.

The time in the original spreadsheet is formatted in military time, but
without decimals or colons.

So, I need to change

600 to 6:00
1300 to 13:00

etc.
 
V

Vel

Maybe this is a very basic fact that I should know after 7 years of this, but
why does this work? Are numbers automatically read right to left? I would
have assumed that Format(100, "00/:00") would have reformatted to 10:00, or
given an error, or simply formatted strangely.

KARL DEWEY said:
Format([F6],"00\:00")

Vel said:
Here's what I came up with:

TimeOut: Format(Int([F6]/100) & ":" & Right([F6],2),"Short Time")

Is there anything obviously unnecessary or redundant there? Or is there a
better way to do it?




Vel said:
I need to reformat a time in an import procedure.

The time in the original spreadsheet is formatted in military time, but
without decimals or colons.

So, I need to change

600 to 6:00
1300 to 13:00

etc.
 
K

KARL DEWEY

It appears so and text left to right.

Vel said:
Maybe this is a very basic fact that I should know after 7 years of this, but
why does this work? Are numbers automatically read right to left? I would
have assumed that Format(100, "00/:00") would have reformatted to 10:00, or
given an error, or simply formatted strangely.

KARL DEWEY said:
Format([F6],"00\:00")

Vel said:
Here's what I came up with:

TimeOut: Format(Int([F6]/100) & ":" & Right([F6],2),"Short Time")

Is there anything obviously unnecessary or redundant there? Or is there a
better way to do it?




:

I need to reformat a time in an import procedure.

The time in the original spreadsheet is formatted in military time, but
without decimals or colons.

So, I need to change

600 to 6:00
1300 to 13:00

etc.
 
J

John Spencer MVP

Yes, numbers fill from the right to the left. If you think about it that is
necessary since otherwise formatting 1 as 000 would display 1 as 100 and 10 as
100 and 100 as 100. Probably not a good idea.

The formatting of numbers is a bit tricker than that since there can be
decimals involved so before the decimal is right to left, but after the
decimal is left to right.

Of course, sometimes the format function can drive you a little crazy when it
seems to automatically convert a string like "1234" to a number and then
applies the format. You sometimes get results that are not what you expected.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Maybe this is a very basic fact that I should know after 7 years of this, but
why does this work? Are numbers automatically read right to left? I would
have assumed that Format(100, "00/:00") would have reformatted to 10:00, or
given an error, or simply formatted strangely.

KARL DEWEY said:
Format([F6],"00\:00")

Vel said:
Here's what I came up with:

TimeOut: Format(Int([F6]/100) & ":" & Right([F6],2),"Short Time")

Is there anything obviously unnecessary or redundant there? Or is there a
better way to do it?




:

I need to reformat a time in an import procedure.

The time in the original spreadsheet is formatted in military time, but
without decimals or colons.

So, I need to change

600 to 6:00
1300 to 13:00

etc.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top