Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Leading Zeros; Formatting as Time | Excel Programming | |||
CONVERT NOs TO EAN 8 BY ADDING LEADING ZEROS | Excel Discussion (Misc queries) | |||
Adding leading zeros | Excel Programming | |||
Adding Leading Zeros to Text | Excel Discussion (Misc queries) | |||
Adding leading zeros to a calculated value | Excel Programming |