Reformat Time

  • Thread starter Thread starter Vel
  • Start date Start date
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.
 
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?
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top