Military Time Converting wrong

[sassriverrat]

Hello,
I have a macro (below) that works as it should. However, if I put the time in (using a colon), so 12:00 instead of 1200, it gives me a different time (0:00). Any ideas? I need it to work both sides.

Public Function MilitaryToTime(T1 As Integer)'
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.
'
Dim TT1 As Double


TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
MilitaryToTime = TT1


End Function

Thanks

 

[shg]

Public Function MilitaryToTime(d As Double) As Double
  If d < 1# Then
    MilitaryToTime = d
  Else
    MilitaryToTime = CDate(Format(d, "0\:00"))
  End If
End Function

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

In​

[/td][td="bgcolor:#F3F3F3"]

Out​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

12:30​

[/td][td="bgcolor:#CCFFCC"]

12:30​

[/td][td="bgcolor:#CCFFCC"]B1: =MilitaryToTime(A1)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1230​

[/td][td="bgcolor:#CCFFCC"]

12:30​

[/td][td][/td][/tr]
[/table]

 

[Rick Rothstein]

What is happening is Excel is changing the value in the cell you enter 12:00 into a real Time value and changing the cell's format to a Time format as well. If you change the format for the cell you will be inputting your values in to Text (which will stop Excel from interpreting 12:00 as a Time value), you should then be able to use this UDF (user defined function) to get the serial Time value you want...

Function MilitaryToTime(MilTime As String) As Date
  MilitaryToTime = Format(Replace(MilTime, ":", ""), "00:00")
End Function

 

[sassriverrat]

Excellent. I tweaked my code to change it's function to miltime and then added this as another function. Didn't have to change any of my sheet coding. You guys are great. Thanks!

 

[sassriverrat]

Good Afternoon and Happy Holidays!

Come to find out that I am still have some problems. Below are both of my macros so that you are aware of EVERYTHING. I've got an ETA calculator that uses inputs from R28 (Time) and S28 (Date) to calculate (simple distance/time=speed) a speed required. However, now with these changed to the miltime it errors when I put a time into the time (R28) box. If that time does not have a colon than it errors. Ideas?


ETA Calc

Sub ETA_CALC1()

Dim Path1 As Date
Dim Path2 As Date
Dim Path3 As Double
Dim Path4 As Double
Dim Path5 As Double
Dim path6 As Double
Dim Path7 As Date
Dim Path8 As Date
Dim DTG As Double
Dim resp As Integer
'This says that the Distance to go should be in D10 but if we want to use a different mileage than what today's report had list, _
(D10 is on the list), we can input into S32 to use our own distance


'This piece ensures "ETA Arrival ZD" is ready
Worksheets("Developer").Range("F3").FormulaR1C1 = "=IF(AND((Notes!R[10]C[6]+Notes!R[10]C[7])<(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3])),(Notes!R[11]C[6]+Notes!R[11]C[7])>(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3]))),""Yes"",""No"")"


If ActiveSheet.Range("S29").Value = "" Then
    DTG = ActiveSheet.Range("D10").Value
    Else: DTG = ActiveSheet.Range("S29").Value
End If


Path1 = ActiveSheet.Range("R28").Value
Path2 = ActiveSheet.Range("T28").Value
Path3 = DTG


Path5 = Sheets("Developer").Range("G2").Value
path6 = ActiveSheet.Range("C5").Value
Path7 = ActiveSheet.Range("F4").Value
Path8 = ActiveSheet.Range("D4").Value


Path4 = (Path3 / (((Path2 + Path1 + (TimeSerial(Path5, 0, 0))) - (Path7 + Path8 + (TimeSerial(path6, 0, 0)))) * 24))




resp = MsgBox("Based on your desired Arrival Time/Date and your mileage input, your speed required to make your ETA is: " & Round(Path4, 1) & " knots" & vbCrLf & vbCrLf & "Would you like to use this ETA for Today's Report?", vbYesNo)
If resp = vbYes Then
    ActiveSheet.Range("W33").Value = Format(Path1, "hh:mm;@")
    ActiveSheet.Range("Y33:Z33").Value = Path2
    End If


End Sub

Function MilTime(T1 As Integer)
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.


Dim TT1 As Double
TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
MilTime = TT1


End Function


Function MilitaryToTime(MilTime As String) As Date
  MilitaryToTime = Format(Replace(MilTime, ":", ""), "00:00")
End Function

 

[sassriverrat]

Hey @Rick Rothstein

So half of the problem is fixed- I inserted your change from the other forum for the intersect on the timestamp. I also inserted a small piece of code to ensure that the sheet is unprotected when the ETA calc button is pushed (macro run) just to be absolutely sure. The calculator seems to work as it should IF the time is put in with a colon. However, if the time is put in without a colon, the calculator fails and spits out a 0. I also notice that when it spits a zero out and asks if this is the time/date I want to use for the ETA, if I click "Yes" and therefore transfer it to the W33 cell, the time doesn't transfer correctly. SO to simplify-

Time inputted into ETA Calc as hh:mm, works perfectly and transfers perfectly
Time inputted into ETA calc as hhmm, spits out a 0 and transfers to W33 as "00:00"

The idea is that the time can be inputted into the ETA calc (R28) in either format.

Thanks

 

[Rick Rothstein]

Hey @Rick Rothstein

So half of the problem is fixed- I inserted your change from the other forum for the intersect on the timestamp. I also inserted a small piece of code to ensure that the sheet is unprotected when the ETA calc button is pushed (macro run) just to be absolutely sure. The calculator seems to work as it should IF the time is put in with a colon. However, if the time is put in without a colon, the calculator fails and spits out a 0. I also notice that when it spits a zero out and asks if this is the time/date I want to use for the ETA, if I click "Yes" and therefore transfer it to the W33 cell, the time doesn't transfer correctly. SO to simplify-

Time inputted into ETA Calc as hh:mm, works perfectly and transfers perfectly
Time inputted into ETA calc as hhmm, spits out a 0 and transfers to W33 as "00:00"

The idea is that the time can be inputted into the ETA calc (R28) in either format.

I think the problem has to do with Excel being "overly helpful". When you enter a real time value into a cell formatted as General, Excel changes the cell format to a Time format. Then when you input a 3 or 4 digit number, excel interprets that as a time value, but because there is no decimal value, it does not see it any time component for what it now assumes is a date. If you format the cell you type the time value into as Text, the function I posted should then work correctly.

 

[sassriverrat]

Makes sense- So I tweaked your code as follows and it's still having the same problem-
Function MilTime(T1 As Integer)
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.


Dim TT1 As Double
TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
MilTime = TT1


End Function


Function MilitaryToTime(MilTime As String) As Date
MilitaryToTime = Format(Replace(MilTime, ":", ""), "00:00")
End Function

 

[sassriverrat]

@Rick Rothstein

I've got it! So I should have realized this before and I'll post this exact same piece (with the code) on the other open forum.

The ETA calc is running it's numbers based on the coding (formula) and since it's not a function in the cell itself, I would imagine excel is running the code and interpreting the time (1230) as a serial, not as the time. So is there a way to run the function within vba? I presume I could write the code with the function spelled in it. As I said, just see the other forum.

 

[sassriverrat]

Sub ETA_CALC1()


Dim Path1 As Date
Dim Path2 As Date
Dim Path3 As Double
Dim Path4 As Double
Dim Path5 As Double
Dim path6 As Double
Dim Path7 As Date
Dim Path8 As Date
Dim DTG As Double
Dim resp As Integer
'This says that the Distance to go should be in D10 but if we want to use a different mileage than what today's report had list, _
(D10 is on the list), we can input into S32 to use our own distance


'This piece ensures "ETA Arrival ZD" is ready
Worksheets("Developer").Range("F3").FormulaR1C1 = "=IF(AND((Notes!R[10]C[6]+Notes!R[10]C[7])<(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3])),(Notes!R[11]C[6]+Notes!R[11]C[7])>(('Voyage Specifics'!R[6]C[-3]+'Voyage Specifics'!R[7]C[-3]))),""Yes"",""No"")"


If ActiveSheet.Range("S29").Value = "" Then
    DTG = ActiveSheet.Range("D10").Value
    Else: DTG = ActiveSheet.Range("S29").Value
End If


Path1 = ActiveSheet.Range("R28").Value
Path2 = ActiveSheet.Range("T28").Value
Path3 = DTG




Path5 = Sheets("Developer").Range("G2").Value
path6 = ActiveSheet.Range("C5").Value
Path7 = ActiveSheet.Range("F4").Value
Path8 = ActiveSheet.Range("D4").Value


Path4 = (Path3 / (((Path2 + Path1 + (TimeSerial(Path5, 0, 0))) - (Path7 + Path8 + (TimeSerial(path6, 0, 0)))) * 24))
'So here with path 1 - Putting a time in with a colon works perfectly- putting a time in as a military time (eg 1230) spits back a "0.00" answer, which
I presume is excel reading the value incorrectly. Can I adjust part of the code and replace Path1 with MilitaryToTime(Path1) ?




resp = MsgBox("Based on your desired Arrival Time/Date and your mileage input, your speed required to make your ETA is: " & Round(Path4, 1) & " knots" & vbCrLf & vbCrLf & "Would you like to use this ETA for Today's Report?", vbYesNo)
If resp = vbYes Then
    ActiveSheet.Range("W33").Value = Format(Path1, "hh:mm;@")
    ActiveSheet.Range("Y33:Z33").Value = Path2
    End If
End Sub