Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
i have a spreadsheet with different arrival and departure times for a
airline. the times are entered in military time, and i was wondering i there is a way to automatically convert the military time without colon into military time with a colon, or normal time with a colon also, is it possible to do this when data is pasted into a cell? i hav tried using the following vba script, but i have to double click o each cell individually. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim TimeStr As String On Error GoTo EndMacro If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub End If If Target.Cells.Count 1 Then Exit Sub End If If Target.Value = "" Then Exit Sub End If Application.EnableEvents = False With Target If .HasFormula = False Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "00:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "00:" & .Value Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End With Application.EnableEvents = True Exit Sub EndMacro: MsgBox "You did not enter a valid time" Application.EnableEvents = True End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
Soccerstar,
Instead of the change event, try a bulk conversion. Select all the cells you need to convert, and run the sub below. HTH, Bernie MS Excel MVP Sub ConvertToTimes() Dim myCell As Range Dim TimeStr As String Application.EnableEvents = False On Error GoTo Invalid For Each myCell In Selection With myCell If myCell.Value < "" Then If Not .HasFormula Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "0" & .Value & ":00" Case 2 ' e.g., 12 = 00:12 AM TimeStr = .Value & ":00" Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select ..Value = TimeValue(TimeStr) End If End If End With Invalid: Next myCell Application.EnableEvents = True End Sub "soccerstr1 " wrote in message ... i have a spreadsheet with different arrival and departure times for an airline. the times are entered in military time, and i was wondering if there is a way to automatically convert the military time without a colon into military time with a colon, or normal time with a colon. also, is it possible to do this when data is pasted into a cell? i have tried using the following vba script, but i have to double click on each cell individually. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
Sorry, I may have confused your intent: when you have a one or two digits, I
assumed hours. If you want the default to be minutes, change: Case 1 ' e.g., 1 = 00:01 AM TimeStr = "0" & .Value & ":00" Case 2 ' e.g., 12 = 00:12 AM TimeStr = .Value & ":00" back to Case 1 ' e.g., 1 = 00:01 AM TimeStr = "0:0" & .Value Case 2 ' e.g., 12 = 00:12 AM TimeStr = "0:" & .Value Sorry about that, Bernie MS Excel MVP "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Soccerstar, Instead of the change event, try a bulk conversion. Select all the cells you need to convert, and run the sub below. HTH, Bernie MS Excel MVP Sub ConvertToTimes() Dim myCell As Range Dim TimeStr As String Application.EnableEvents = False On Error GoTo Invalid For Each myCell In Selection With myCell If myCell.Value < "" Then If Not .HasFormula Then Select Case Len(.Value) Case 1 ' e.g., 1 = 00:01 AM TimeStr = "0" & .Value & ":00" Case 2 ' e.g., 12 = 00:12 AM TimeStr = .Value & ":00" Case 3 ' e.g., 735 = 7:35 AM TimeStr = Left(.Value, 1) & ":" & _ Right(.Value, 2) Case 4 ' e.g., 1234 = 12:34 TimeStr = Left(.Value, 2) & ":" & _ Right(.Value, 2) Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45 TimeStr = Left(.Value, 1) & ":" & _ Mid(.Value, 2, 2) & ":" & Right(.Value, 2) Case 6 ' e.g., 123456 = 12:34:56 TimeStr = Left(.Value, 2) & ":" & _ Mid(.Value, 3, 2) & ":" & Right(.Value, 2) Case Else Err.Raise 0 End Select .Value = TimeValue(TimeStr) End If End If End With Invalid: Next myCell Application.EnableEvents = True End Sub "soccerstr1 " wrote in message ... i have a spreadsheet with different arrival and departure times for an airline. the times are entered in military time, and i was wondering if there is a way to automatically convert the military time without a colon into military time with a colon, or normal time with a colon. also, is it possible to do this when data is pasted into a cell? i have tried using the following vba script, but i have to double click on each cell individually. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
ok, not to sound dumb, but where would i paste this sub? not to familia
with macro -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
Soccer Star,
Use Alt=F11 to go into the VBE (Visual Basic Editor), the use Ctrl-R to open the project explorer, select your workbook, then use Insert | Module, and paste the code into the big window that appears. Then you can go back to Excel (alt-F11 again) and use Tools | Macro | Macros... then select "Macros In.." and choose "This Workbook". Then select the macro and run it. You can also assign the macro to a drawing object, or to a custom commandbar button. HTH, Bernie MS Excel MVP "soccerstr1 " wrote in message ... ok, not to sound dumb, but where would i paste this sub? not to familiar with macros --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
ok, i tried running the macro and it didn't do anything. should thi
line have the periods in the beginning? ..Value = TimeValue(TimeStr) i tried leaving them in, but i got an error message. i then removed th periods and the macro was selected. i then creasted the cells i neede to edit, and then ran the macro and nothing happened to the selecte cells after i ran the macr -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
SoccerStar,
Send me a message privately and I will send you a working example workbook. HTH, Bernie MS Excel MVP "soccerstr1 " wrote in message ... ok, i tried running the macro and it didn't do anything. should this line have the periods in the beginning? .Value = TimeValue(TimeStr) i tried leaving them in, but i got an error message. i then removed the periods and the macro was selected. i then creasted the cells i needed to edit, and then ran the macro and nothing happened to the selected cells after i ran the macro --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
Bernie, i have sent a a pm. hope to hear from you soon. thanx agai
-- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
If you scatully sent it, I didn't get it: you need to take out the spaces
and change the dot to a "." in my email address. Bernie "soccerstr1 " wrote in message ... Bernie, i have sent a a pm. hope to hear from you soon. thanx again --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
I _really_ need to work on my typing - - "If you _actually_ sent it......"
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... If you scatully sent it, I didn't get it: you need to take out the spaces and change the dot to a "." in my email address. Bernie "soccerstr1 " wrote in message ... Bernie, i have sent a a pm. hope to hear from you soon. thanx again --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
bernie, i was just wondering if u have received my email i sen
yesterday -- Message posted from http://www.ExcelForum.com |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
No. Enter this Excel formula in a cell:
="deitbe" & "@" & "consumer" & ".org" Then press F2, select the whole formula, and press F9, then Ctrl-C to copy my valid email address... ;-) Bernie "soccerstr1 " wrote in message ... bernie, i was just wondering if u have received my email i sent yesterday? --- Message posted from http://www.ExcelForum.com/ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem with military time and making calculations
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Standard Time to Military Time in Excel | Excel Discussion (Misc queries) | |||
military time calculations | Excel Discussion (Misc queries) | |||
Time sheet template military hours problem | Excel Discussion (Misc queries) | |||
formula for converting military time to standard time, etc | Excel Discussion (Misc queries) | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions |