Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 10
Default Adding Leading Zeros; Formatting as Time

I have columns of text data representing military time (i.e. 1500, 900, 730,
45). The below code shell is designed to determine how many digits are
present and then perform come "action". I need to write the code for the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45 AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves inserting
columns in the native data to accept the reformed data. My goal is to simply
select the columns that need to be reformed and then run the code.




Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0



' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Adding Leading Zeros; Formatting as Time

You could use a formula like:
=--TEXT(A1,"00\:00")
and then format the cell with a time format:
h:mm AM/PM

And the value will be a real time (nice for further calculations)

Or you could use:
=TEXT(TEXT(A1,"00\:00"),"h:mm AM/PM"
to return text

In code, you could do something like this to return a real time:

Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "h:mm AM/PM"
.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100, 0)
End With
Next myCell

End Sub

Or this to return text:
Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@" 'text
.Value = Format(TimeSerial(myCell.Value \ 100, _
myCell.Value Mod 100, 0), "h:mm AM/PM")
End With
Next myCell

End Sub





D. Stacy wrote:

I have columns of text data representing military time (i.e. 1500, 900, 730,
45). The below code shell is designed to determine how many digits are
present and then perform come "action". I need to write the code for the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45 AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves inserting
columns in the native data to accept the reformed data. My goal is to simply
select the columns that need to be reformed and then run the code.

Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0


' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 245
Default Adding Leading Zeros; Formatting as Time

Dave

I like the formatting solution. Do you any references where I can learn a
little more?

--
Steve

"Dave Peterson" wrote in message
...
You could use a formula like:
=--TEXT(A1,"00\:00")
and then format the cell with a time format:
h:mm AM/PM

And the value will be a real time (nice for further calculations)

Or you could use:
=TEXT(TEXT(A1,"00\:00"),"h:mm AM/PM"
to return text

In code, you could do something like this to return a real time:

Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "h:mm AM/PM"
.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100,
0)
End With
Next myCell

End Sub

Or this to return text:
Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@" 'text
.Value = Format(TimeSerial(myCell.Value \ 100, _
myCell.Value Mod 100, 0), "h:mm AM/PM")
End With
Next myCell

End Sub





D. Stacy wrote:

I have columns of text data representing military time (i.e. 1500, 900,
730,
45). The below code shell is designed to determine how many digits are
present and then perform come "action". I need to write the code for the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45 AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves inserting
columns in the native data to accept the reformed data. My goal is to
simply
select the columns that need to be reformed and then run the code.

Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0


' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub


--

Dave Peterson


  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default Adding Leading Zeros; Formatting as Time

You should be able to replace this statement from your VBA code...

..Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100, 0)

with this simpler statement...

..Value = CDate(Format(myCell.Value, "00:00"))

--
Rick (MVP - Excel)


"Dave Peterson" wrote in message
...
You could use a formula like:
=--TEXT(A1,"00\:00")
and then format the cell with a time format:
h:mm AM/PM

And the value will be a real time (nice for further calculations)

Or you could use:
=TEXT(TEXT(A1,"00\:00"),"h:mm AM/PM"
to return text

In code, you could do something like this to return a real time:

Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "h:mm AM/PM"
.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100,
0)
End With
Next myCell

End Sub

Or this to return text:
Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@" 'text
.Value = Format(TimeSerial(myCell.Value \ 100, _
myCell.Value Mod 100, 0), "h:mm AM/PM")
End With
Next myCell

End Sub





D. Stacy wrote:

I have columns of text data representing military time (i.e. 1500, 900,
730,
45). The below code shell is designed to determine how many digits are
present and then perform come "action". I need to write the code for the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45 AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves inserting
columns in the native data to accept the reformed data. My goal is to
simply
select the columns that need to be reformed and then run the code.

Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0


' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub


--

Dave Peterson


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Adding Leading Zeros; Formatting as Time

For the =text() worksheet function, check excel's help.

For VBA's Format, check VBA's help.



AltaEgo wrote:

Dave

I like the formatting solution. Do you any references where I can learn a
little more?

--
Steve

"Dave Peterson" wrote in message
...
You could use a formula like:
=--TEXT(A1,"00\:00")
and then format the cell with a time format:
h:mm AM/PM

And the value will be a real time (nice for further calculations)

Or you could use:
=TEXT(TEXT(A1,"00\:00"),"h:mm AM/PM"
to return text

In code, you could do something like this to return a real time:

Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "h:mm AM/PM"
.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100,
0)
End With
Next myCell

End Sub

Or this to return text:
Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@" 'text
.Value = Format(TimeSerial(myCell.Value \ 100, _
myCell.Value Mod 100, 0), "h:mm AM/PM")
End With
Next myCell

End Sub





D. Stacy wrote:

I have columns of text data representing military time (i.e. 1500, 900,
730,
45). The below code shell is designed to determine how many digits are
present and then perform come "action". I need to write the code for the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45 AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves inserting
columns in the native data to accept the reformed data. My goal is to
simply
select the columns that need to be reformed and then run the code.

Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0


' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub


--

Dave Peterson


--

Dave Peterson


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 35,218
Default Adding Leading Zeros; Formatting as Time

Or even replace this:
.Value = TimeSerial(.Value \ 100, .Value Mod 100, 0)
with
.Value = CDate(Format(.Value, "00:00"))
or even
.Value = Format(.Value, "00:00")

(too many myCell's left in the original code.)

But it bothers me (a personal bother) that 00:00 is treated as hh:mm, not
mm:ss. And I don't trust my memory (or excel) to not use something like:

.Value = Format(.Value, "00:00") & ":00"

=======
Please don't ask why I trusted =text() though!

Inconsistencies are good for the soul <vbg.

Rick Rothstein wrote:

You should be able to replace this statement from your VBA code...

.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100, 0)

with this simpler statement...

.Value = CDate(Format(myCell.Value, "00:00"))

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
You could use a formula like:
=--TEXT(A1,"00\:00")
and then format the cell with a time format:
h:mm AM/PM

And the value will be a real time (nice for further calculations)

Or you could use:
=TEXT(TEXT(A1,"00\:00"),"h:mm AM/PM"
to return text

In code, you could do something like this to return a real time:

Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "h:mm AM/PM"
.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod 100,
0)
End With
Next myCell

End Sub

Or this to return text:
Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@" 'text
.Value = Format(TimeSerial(myCell.Value \ 100, _
myCell.Value Mod 100, 0), "h:mm AM/PM")
End With
Next myCell

End Sub





D. Stacy wrote:

I have columns of text data representing military time (i.e. 1500, 900,
730,
45). The below code shell is designed to determine how many digits are
present and then perform come "action". I need to write the code for the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45 AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves inserting
columns in the native data to accept the reformed data. My goal is to
simply
select the columns that need to be reformed and then run the code.

Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0


' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub


--

Dave Peterson


--

Dave Peterson
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 245
Default Adding Leading Zeros; Formatting as Time

Dave,

actually, I had in mind a few samples or different applications for
formatting using TEXT to open up a mind that was locked into the Access
Format function solely used for time and date. Having given it more thought
time yesterday, I generated enough ideas to to understand potential
alternate application of TEXT - nothing focuses the mind like a sixty minute
run!

--
Steve

"Dave Peterson" wrote in message
...
For the =text() worksheet function, check excel's help.

For VBA's Format, check VBA's help.



AltaEgo wrote:

Dave

I like the formatting solution. Do you any references where I can learn a
little more?

--
Steve

"Dave Peterson" wrote in message
...
You could use a formula like:
=--TEXT(A1,"00\:00")
and then format the cell with a time format:
h:mm AM/PM

And the value will be a real time (nice for further calculations)

Or you could use:
=TEXT(TEXT(A1,"00\:00"),"h:mm AM/PM"
to return text

In code, you could do something like this to return a real time:

Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "h:mm AM/PM"
.Value = TimeSerial(myCell.Value \ 100, myCell.Value Mod
100,
0)
End With
Next myCell

End Sub

Or this to return text:
Option Explicit
Sub testme()
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.NumberFormat = "@" 'text
.Value = Format(TimeSerial(myCell.Value \ 100, _
myCell.Value Mod 100, 0), "h:mm AM/PM")
End With
Next myCell

End Sub





D. Stacy wrote:

I have columns of text data representing military time (i.e. 1500,
900,
730,
45). The below code shell is designed to determine how many digits
are
present and then perform come "action". I need to write the code for
the
"action", now that the rest of this is working.

The desired output would look like : 3:00 PM, 9:00 AM, 7:30 AM, 12:45
AM

In excel, using formulas, this is accomplished using a mega formula
consisting of a lot of if statements, etc. etc. And it invloves
inserting
columns in the native data to accept the reformed data. My goal is to
simply
select the columns that need to be reformed and then run the code.

Sub ConfigureTimeData()
' Converts text time data to 4 digit time data
Dim ConstantCells As Range
Dim Cell As Range
Dim Length As Double

If TypeName(Selection) < "Range" Then Exit Sub
Application.ScreenUpdating = False

' Create subsets of original selection to avoid processing empty
cells
On Error Resume Next
Set ConstantCells = Selection.SpecialCells(xlConstants)

On Error GoTo 0


' Process the constant cells
If Not ConstantCells Is Nothing Then
For Each Cell In ConstantCells
Select Case Len(Cell.Text)

Case 0
Cell.Interior.Pattern = xlNone
Cell.Value = " "
Case 1
Cell.Interior.Pattern = xlNone

Case 2
Cell.Interior.Pattern = xlNone
Cell.Value = "Two"
Case 3
Cell.Interior.Pattern = xlNone
Cell.Value = "Three"
Case 4
Cell.Interior.Pattern = xlNone
Cell.Value = "Four"
Case Is 4
Cell.Interior.Color = RGB(255, 0, 255)
Cell.Value = "ERROR"
End Select



Next Cell
End If



End Sub

--

Dave Peterson


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding Leading Zeros; Formatting as Time AltaEgo Excel Programming 0 May 4th 09 02:26 AM
CONVERT NOs TO EAN 8 BY ADDING LEADING ZEROS pdb Excel Discussion (Misc queries) 7 February 13th 09 08:46 PM
Adding leading zeros SITCFanTN Excel Programming 3 July 10th 07 07:12 PM
Adding Leading Zeros to Text Jenn Excel Discussion (Misc queries) 4 January 12th 05 07:51 PM
Adding leading zeros to a calculated value John Johndon Excel Programming 2 July 12th 03 06:25 PM


All times are GMT +1. The time now is 05:06 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"