Military Time Converting wrong

sassriverrat · Nov 24, 2018

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.

Code:

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

sassriverrat · Dec 6, 2018

So @Rick Rothstein

So still kicking an errors. It is computing but it isn't giving me the correct answer. If I input 0300 into the time cell (R28), the cell shows "300" and after putting a date in, it will kick back an answer of 0.0 in the msg box. If I input 03:00 into R28, I get the right answer. The military time piece works perfectly within the userforms to input times into a userform text box and have it spit out on the sheet but it isn't working for data put into the excel sheet directly. Ideas?

Rick Rothstein · Dec 6, 2018

sassriverrat said:
So @Rick Rothstein

So still kicking an errors. It is computing but it isn't giving me the correct answer. If I input 0300 into the time cell (R28), the cell shows "300" and after putting a date in, it will kick back an answer of 0.0 in the msg box. If I input 03:00 into R28, I get the right answer. The military time piece works perfectly within the userforms to input times into a userform text box and have it spit out on the sheet but it isn't working for data put into the excel sheet directly. Ideas?

Did you format the cells you enter your military time in as Text (not General or Date)?

sassriverrat · Dec 7, 2018

I have tried but it didn't change the answer. Do I need to run the function for that calculator within the code? If so, can it just be inputted into the code?

sassriverrat · Dec 8, 2018

bump?

Rick Rothstein · Dec 8, 2018

sassriverrat said:
I have tried but it didn't change the answer.

I am not sure what to tell you... what I posted works for me when the cells the military time is place in is formatted as Text beforehand.

sassriverrat · Dec 8, 2018

So to check on my errors- where did you put your function? Not in the vba for the sheet, itself, right?

I put it in the ETA calcs module

Rick Rothstein · Dec 8, 2018

sassriverrat said:
I put it in the ETA calcs module

You can place it in any general (non-sheet, non-ThisWorkbook) module, so the one you put the ETA_CALC1 macro in should be fine.

sassriverrat · Dec 9, 2018

Rick!

Found it! I'm not sure why excel wasn't debugging on me but I had a bracket [ in there where there should have been a parenthesis. Anyway- changed that and it's working perfectly. Attention to detail.....Thanks again, seriously!

sassriverrat · Dec 10, 2018

So now a new problem arises- I'm getting an error '13' when trying to create a noon sheet. My arrival sheet creator is working perfectly and using your piece but the noon sheet creator bugs out before it can reach the end. Maybe you notice something in here I don't? It's bugging out and highlighting the 'militarytotime = format(replacemiltime' piece.

miltime functions

Code:

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

noon creator

Code:

Sub AddNoonSheet()
'Speeds up the formating part
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False


'Adds additional Noon Sheets
    Sheets.Add(After:=Sheets("Voyage Specifics")).Name = "Noon"


'This creates all of the proper row heights/column widths
  Rows("1:55").RowHeight = 15
    Columns("A:A").ColumnWidth = 3.29
    Columns("B:B").ColumnWidth = 1.43
    Columns("C:C").ColumnWidth = 5.57
    Columns("D:D").ColumnWidth = 9.14
    Columns("E:G").ColumnWidth = 2.8
    Columns("H:H").ColumnWidth = 7.29
    Columns("I:L").ColumnWidth = 8.43
    Columns("M:M").ColumnWidth = 9.14
    Columns("N:N").ColumnWidth = 7.86
    Columns("O:O").ColumnWidth = 3.14
    Columns("P:P").ColumnWidth = 4
    Columns("Q:Q").ColumnWidth = 20
    Columns("R:R").ColumnWidth = 8.5
    Columns("S:S").ColumnWidth = 2.5
    Columns("T:T").ColumnWidth = 8
    Columns("U:U").ColumnWidth = 2
    Columns("V:V").ColumnWidth = 13
    Columns("W:W").ColumnWidth = 5
    Columns("X:X").ColumnWidth = 3
    Columns("Y:Y").ColumnWidth = 6
    Columns("Z:Z").ColumnWidth = 3.5
    
'This creates the appropriate printer border sheets and daily inputs
'Company Title
    Range("A1:O1,A20:O20,A39:O39").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Vessel Name
    Range("A2:O2,A21:O21,A40:O40").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Noon/Arrival Report
    Range("A3:O3,A22:O22,A41:O41").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Time/Date
    Range("A4:C4,A23:C23,A42:C42").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'ZD
    Range("C5,C24,C43").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'Date
    Range("F4:H4,F23:H23,F42:H42").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Port + Selected Port
    Range("I4:J4,I23:J23,I42:J42").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Selected Port
    Range("I5:J5,I24:J24,I43:J43").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Voyage #, Course, Speed, Trip Distance, Total Distance, TAS, Spd Req, Voyage Total Dist
    Range("L4:M11,L23:M30,L42:M49").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge True
'Latitude, Longitude, Hours Run, Total Hours, DTG, ETA, Wind, Wave, Swell, Temp, Pressure
    Range("A6:C16,A25:C35,A44:C54").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge True
'Latitude & Longitude Left Alignment
    Range("D6,D7,D25,D26,D44,D45").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
'ETA Date
    Range("F11:H11,F30:H30,F49:H49").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Heavy Weather
    Range("J12:L13,J31:L32,J50:L51").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Slow Steaming
    Range("J14:L15,J33:L34,J52:L53").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Watch Officer
    Range("A17:I17,A36:I36,A55:I55").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Input in Yellow
    Range("Q4:S4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'ETA Calculators
    Range("Q27:T27").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("Q29:R29,Q31:R31,Q32:R32").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("S28,S29:T29,S31:T31,S32:T32").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("P28:P29,P31:P32").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
'Voyage Report Indicators
    Range("V20:Y22").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge True
'Override Inputs and Voyage Report Indicators
    Range("Z20,Z21,Z22,V23:Z23,W24:Z24,W25:Z25,X28:Y28,X29:Y29,X30:Y30,X31:Y31,X32:Y32,X33,Y33:Z33").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("V28:W28,V29:W29,V30:W30,V31:W31,V32:W32,V34:X34").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
    Range("Y28").Select
'Borders out the Noon Report Cells
    Range("O1:O17,I12:I15,O20:O36,I31:I34,O39:O55,I50:I53").Activate
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
     Range("O1:O17,O20:O36,O39:O55").Activate
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    Range("A1:A17,A20:A36,A39:A55").Activate
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
   
    Range("A17:O17,A36:O36,A55:O55,J13:O13,J32:O32,J51:O51,J15:O15,J34:O34,J53:O53").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    Range("A3:O3,A22:O22,A41:O41,A5:E5,A6:E6,A24:E24,A25:E25,A43:E43,A44:E44,A45:O45,A7:O7,A26:O26,A28:O28,A30:O30,A47:O47,A9:O9,A11:O11,A49:O49,M12:O12,M31:O31,M33:O33,M14:O14,M50:O50,M52:O52").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    
    Range("E6:E7,E25:E26,E44:E45").Select
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Borders out the Input in Yellow Cells
    Range("R5:R16,R19:R20,R23:R24,S6:S7").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
'Borders out the ETA Calculators
Range("Q27:T29,Q31:T32").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Borders out the Voyage Report Indicators
    Range("V20:Z22").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Borders out the Manual Input Cells
    Range("V23:Z34").Select
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
'Colors Voyage Report Indicators
    If Range("Z20") = "Yes" Then
        Interior.Color = 5287936
    ElseIf Range("Z20") = "No" Then
        Range("Z20").Interior.Color = vbRed
    End If
    If Range("Z21") = "Yes" Then
        Interior.Color = 5287936
    ElseIf Range("Z21") = "No" Then
        Range("Z21").Interior.Color = 15773696
    End If
    If Range("Z22") = "L" Then
        Interior.Color = 5287936
    ElseIf Range("Z22") = "B" Then
        Range("Z22").Interior.Color = 15773696
    End If
        
        
    
    Range("H12:H15,H31:H34,H50:H53").Borders(xlEdgeRight).LineStyle = xlNone


'Hides the Manual Inputs
    Range("V23:Z34").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("V24:Z34").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
     
'Creates Fonts for Entire Sheet
   'Font for Company
    
    Range("A1:O1,A20:O20,A39:O39").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "9"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With


   'Font for Vessel Name
    Range("A2:O2,A21:O21,A40:O40").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
   'Font for Noon/Arrival
    Range("A3:O3,A22:O22,A41:O41").Select
    With Selection.Font
        .Bold = True
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "14"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Font for Rest of the Sheet
    Range("A4:O17,A23:O36,A42:O55,P1:Z55").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With


    'Font for Input in Yellow
    Range("Q4:S4").Select
    With Selection.Font
        .Underline = xlUnderlineStyleSingle
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Font for Heavy Weather and Slow Steaming
    Range("Q18,Q22").Select
    With Selection.Font
        .Underline = xlUnderlineStyleSingle
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
   
   'Font for Rest of the Sheet
    Range("P1:Z17,P18:P55,R18:Z55,Q19:Q21,Q23:Q55").Select
    With Selection.Font
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Bold for Voyage Report Indicators and Manual Inputs
     Range("Z20:Z22,V23:Z23").Select
    With Selection.Font
        .Bold = True
        .Name = "Times New Roman"
        .Strikethrough = False
        .Size = "12"
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    
    'Aligns ETA Dates
    Range("E11,E30,E49").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("F11:H11,F30:H30,F49:H49").Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With
    
    'Aligns Weather Reports
     Range("D12:D16,D31:D35,D50:D54").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
    'Aligns Inputs in Yellow
    Range("R5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("R6:R16").Select
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
    End With


    
' Inputs all of the Cell Data for the Noon Report
'


'All the formula titles
    Range("A1:O1,A20:O20,A39:O39").FormulaR1C1 = "OSG Ship Management Inc."
    Range("A2:O2,A21:O21,A40:O40").FormulaR1C1 = "=Notes!R22C11"
    Range("A3:O3,A22:O22,A41:O41").FormulaR1C1 = "Noon Report"
    Range("A4:C4,A23:C23,A42:C42").FormulaR1C1 = "Time/Date:"
    Range("E4,E23,E42").FormulaR1C1 = "/"
    Range("A5,A24,A43").FormulaR1C1 = "ZD:"
    Range("A6:C6,A25:C25,A44:C44").FormulaR1C1 = "Latitude:"
    Range("A7:C7,A26:C26,A45:C45").FormulaR1C1 = "Longitude:"
    Range("A8:C8,A27:C27,A46:C46").FormulaR1C1 = "Hours Run:"
    Range("E8,E27,E46").FormulaR1C1 = "h"
    Range("G8,G27,G46").FormulaR1C1 = "m"
    Range("A9:C9,A28:C28,A47:C47").FormulaR1C1 = "Total Hours:"
    Range("E9,E28,E47").FormulaR1C1 = "h"
    Range("G9,G28,G47").FormulaR1C1 = "m"
    Range("A10:C10,A29:C29,A48:C48").FormulaR1C1 = "DTG:"
    Range("E10,E29,E48").FormulaR1C1 = "nm"
    Range("A11:C11,A30:C30,A49:C49").FormulaR1C1 = "ETA:"
    Range("E11,E30,E49").FormulaR1C1 = "/"
    Range("A12:C12,A31:C31,A50:C50").FormulaR1C1 = "Wind:"
    Range("A13:C13,A32:C32,A51:C51").FormulaR1C1 = "Wave:"
    Range("A14:C14,A33:C33,A52:C52").FormulaR1C1 = "Swell:"
    Range("A15:C15,A34:C34,A53:C53").FormulaR1C1 = "Temp:"
    Range("A16:C16,A35:C35,A54:C54").FormulaR1C1 = "Pressure:"
    Range("A17:I17,A36:I36,A55:I55").FormulaR1C1 = "Watch Officer"
    Range("I4:J4,I23:J23,I42:J42").FormulaR1C1 = "Arrival Port:"
    Range("L4:M4,L23:M23,L42:M42").FormulaR1C1 = "Voyage:"
    Range("L5:M5,L24:M24,L43:M43").FormulaR1C1 = "Course:"
    Range("O5,O24,O43").FormulaR1C1 = "°"
    Range("L6:M6,L25:M25,L44:M44").FormulaR1C1 = "Speed:"
    Range("O6,O25,O44").FormulaR1C1 = "kts"
    Range("L7:M7,L26:M26,L45:M45").FormulaR1C1 = "Trip Distance:"
    Range("O7,O26,O45").FormulaR1C1 = "nm"
    Range("L8:M8,L27:M27,L46:M46").FormulaR1C1 = "Total Distance:"
    Range("O8,O27,O46").FormulaR1C1 = "nm"
    Range("L9:M9,L28:M28,L47:M47").FormulaR1C1 = "TAS:"
    Range("O9,O28,O47").FormulaR1C1 = "kts"
    Range("L10:M10,L29:M29,L48:M48").FormulaR1C1 = "Spd Req:"
    Range("O10,O29,O48").FormulaR1C1 = "kts"
    Range("L11:M11,L30:M30,L49:M49").FormulaR1C1 = "Voyage Tot Dist:"
    Range("O11,O30,O49").FormulaR1C1 = "nm"
    Range("J12:L13,J31:L32,J50:L51").FormulaR1C1 = "Heavy Weather:"
    Range("J14:L15,J33:L34,J52:L53").FormulaR1C1 = "Slow Steaming:"
    Range("L12:M12,L31:M31,L50:M50").FormulaR1C1 = "Time:"
    Range("O12,O31,O50").FormulaR1C1 = "hrs"
    Range("L13:M13,L32:M32,L51:M51").FormulaR1C1 = "Distance:"
    Range("O13,O32,O51").FormulaR1C1 = "nm"
    Range("L14:M14,L33:M33,L52:M52").FormulaR1C1 = "Time:"
    Range("O14,O33,O52").FormulaR1C1 = "hrs"
    Range("L15:M15,L34:M34,L53:M53").FormulaR1C1 = "Distance:"
    Range("O15,O34,O53").FormulaR1C1 = "nm"
    
    Range("Q4:S4").FormulaR1C1 = "Input in Yellow"
    Range("Q5").FormulaR1C1 = "Zone Description:"
    Range("Q6").FormulaR1C1 = "Latitude"
    Range("S6").FormulaR1C1 = "N"
    Range("Q7").FormulaR1C1 = "Longitude"
    Range("S7").FormulaR1C1 = "W"
    Range("Q8").FormulaR1C1 = "Distance to Go"
    Range("S8").FormulaR1C1 = "nm"
    Range("Q9").FormulaR1C1 = "Total Distance"
    Range("S9").FormulaR1C1 = "nm"
    Range("Q10").FormulaR1C1 = "Course:"
    Range("S10").FormulaR1C1 = "°"
    Range("Q11").FormulaR1C1 = "Wind(Dir & Force)"
    Range("Q12").FormulaR1C1 = "Wave(Dir & State):"
    Range("Q13").FormulaR1C1 = "Swell(Dir & Descr):"
    Range("Q14").FormulaR1C1 = "Temperature:"
    Range("S14").FormulaR1C1 = "°"
    Range("Q15").FormulaR1C1 = "Pressure:"
    Range("S15").FormulaR1C1 = "mb"
    Range("Q16").FormulaR1C1 = "Watch Officer:"


    Range("Q18").FormulaR1C1 = "Heavy Weather"
    Range("Q19").FormulaR1C1 = "Time:"
    Range("S19").FormulaR1C1 = "hrs"
    Range("Q20").FormulaR1C1 = "Distance:"
    Range("S20").FormulaR1C1 = "nm"
    
    Range("Q22").FormulaR1C1 = "Slow Steaming:"
    Range("Q23").FormulaR1C1 = "Time:"
    Range("S23").FormulaR1C1 = "hrs"
    Range("Q24").FormulaR1C1 = "Distance:"
    Range("S24").FormulaR1C1 = "nm"
    
    Range("P28:P29").FormulaR1C1 = "'1."
    Range("P31:P32").FormulaR1C1 = "'2."
    
    Range("Q27:T27").FormulaR1C1 = "ETA Calculators"
    Range("Q28").FormulaR1C1 = "Desired Arr Date/Time:"
    Range("S28").FormulaR1C1 = "/"
    Range("Q29:R29").FormulaR1C1 = "Mileage(If Not Today's DTG):"
    Range("Q31:R31").FormulaR1C1 = "Anticipated Average Speed:"
    Range("Q32:R32").FormulaR1C1 = "Mileage(If Not Today's DTG):"
    
    Range("V20:Y20").FormulaR1C1 = "Exact Route Calculator:"
    Range("V21:Y21").FormulaR1C1 = "Daylight Savings In Effect:"
    Range("V22:Y22").FormulaR1C1 = "Voyage Loaded/Ballast:"
    
    Range("V23:Z23").FormulaR1C1 = "Override Inputs"
    Range("V24").FormulaR1C1 = "Time:"
    Range("V25").FormulaR1C1 = "Date:"
    Range("V26").FormulaR1C1 = "Hours Run:"
    Range("X26").FormulaR1C1 = "hrs"
    Range("Z26").FormulaR1C1 = "min"
    Range("V27").FormulaR1C1 = "Total Hours:"
    Range("X27").FormulaR1C1 = "hrs"
    Range("Z27").FormulaR1C1 = "min"
    Range("V28").FormulaR1C1 = "Speed:"
    Range("Z28").FormulaR1C1 = "kts"
    Range("V29:W29").FormulaR1C1 = "Trip Distance:"
    Range("Z29").FormulaR1C1 = "nm"
    Range("V30:W30").FormulaR1C1 = "Total Distance:"
    Range("Z30").FormulaR1C1 = "nm"
    Range("V31:W31").FormulaR1C1 = "Total Average Speed:"
    Range("Z31").FormulaR1C1 = "kts"
    Range("V32").FormulaR1C1 = "Speed Required:"
    Range("Z32").FormulaR1C1 = "kts"
    Range("V33").FormulaR1C1 = "New ETA:"
    Range("X33").FormulaR1C1 = "/"
    Range("V34:W34").FormulaR1C1 = "New Voyage Distance:"
    Range("Z34").FormulaR1C1 = "nm"
    
         
    
'All of the equals for the static cells
'ZD + or -
    Range("B5").FormulaR1C1 = "=IF(R[2]C[3]=""W"",""-"",""+"")"
    Range("B24").FormulaR1C1 = "=R[-19]C"
    Range("B43").FormulaR1C1 = "=R[-19]C"
'12:00 Time
    Range("D4").FormulaR1C1 = "=IF(R[20]C[19]="""",TIME(12,0,0),MilitaryToTime(R[20]C[19]))"
    Range("D23").FormulaR1C1 = "=R[-19]C"
    Range("D42").FormulaR1C1 = "=R[-19]C"
'Date Repeater
    Range("F4:H4").FormulaR1C1 = "=IF(R[21]C[17]="""",""No Data Input"",R[21]C[17])"
    Range("F23:H23").FormulaR1C1 = "=R[-19]C"
    Range("F42:H42").FormulaR1C1 = "=R[-19]C"
'ZD = R5
    Range("C5").FormulaR1C1 = "=RC[15]"
    Range("C24").FormulaR1C1 = "=R[-19]C[15]"
    Range("C43").FormulaR1C1 = "=R[-38]C[15]"
'Latitude = R6
    Range("D6").FormulaR1C1 = "=RC[14]"
    Range("D25").FormulaR1C1 = "=R[-19]C[14]"
    Range("D44").FormulaR1C1 = "=R[-38]C[14]"
'N or S
    Range("E6").FormulaR1C1 = "=RC[14]"
    Range("E25").FormulaR1C1 = "=R[-19]C"
    Range("E44").FormulaR1C1 = "=R[-19]C"
'Longtitude = R7
    Range("D7").FormulaR1C1 = "=RC[14]"
    Range("D26").FormulaR1C1 = "=R[-19]C[14]"
    Range("D45").FormulaR1C1 = "=R[-38]C[14]"
'E or W
    Range("E7").FormulaR1C1 = "=RC[14]"
    Range("E26").FormulaR1C1 = "=R[-19]C"
    Range("E45").FormulaR1C1 = "=R[-19]C"
'Hours Run
    Range("D8").FormulaR1C1 = "=IF(R[-4]C[2]=""No Data Input"",0,IF(R[18]C[19]="""",((INT(ABS((((((R[-4]C+((IF(R[-1]C[1]=""E"",-TIME(R[-3]C[14],0,0),TIME(R[-3]C[14],0,0)))))+R[-4]C[2])-((MilitaryToTime('Voyage Specifics'!R[-2]C[-1])+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R[-5]C[-2]:R[44]C[5],8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1" & _
        ",0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-1])))))))*24)+(HOUR(ABS((((((R[-4]C+((IF(R[-1]C[1]=""E"",-TIME(R[-3]C[14],0,0),TIME(R[-3]C[14],0,0)))))+R[-4]C[2])-((MilitaryToTIme('Voyage Specifics'!R[-2]C[-1])+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R[-5]C[-2]:R[44]C[5]," & _
        "8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-1],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[22]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-1]))))))),R[18]C[19]))" & _
        ""
    Range("D27").FormulaR1C1 = "=R[-19]C"
    Range("D46").FormulaR1C1 = "=R[-19]C"
'Min Run
    Range("F8").FormulaR1C1 = "=IF(R[-4]C=""No Data Input"",0,IF(R[18]C[19]="""",(MINUTE(ABS((((((R[-4]C[-2]+((IF(R[-1]C[-1]=""E"",-TIME(R[-3]C[12],0,0),TIME(R[-3]C[12],0,0)))))+R[-4]C)-((MilitaryToTime('Voyage Specifics'!R[-2]C[-3])+((IF((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R[-5]C[-4]:R[44]C[3],8,FALSE))<0,-TIME(-((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[20]=""Yes"",-" & _
        "1,0))),0,0),TIME(((VLOOKUP('Voyage Specifics'!R[-3]C[-3],Ports!R3C2:R52C9,8,FALSE))+(IF(R[13]C[20]=""Yes"",-1,0))),0,0)))))+'Voyage Specifics'!R[-1]C[-3]))))))),R[18]C[19]))" & _
        ""
    Range("F27").FormulaR1C1 = "=R[-19]C"
    Range("F46").FormulaR1C1 = "=R[-19]C"
'Total Hours Run
    Range("D9").FormulaR1C1 = "=IF(R[18]C[19]="""",R[-1]C,R[18]C[19])"
    Range("D28").FormulaR1C1 = "=R[-19]C"
    Range("D47").FormulaR1C1 = "=R[-19]C"
'Total Min Run
    Range("F9").FormulaR1C1 = "=IF(R[18]C[19]="""",R[-1]C,R[18]C[19])"
    Range("F28").FormulaR1C1 = "=R[-19]C"
    Range("F47").FormulaR1C1 = "=R[-19]C"
'DTG =
    Range("D10").FormulaR1C1 = "=R[-2]C[14]"
    Range("D29").FormulaR1C1 = "=R[-19]C"
    Range("D48").FormulaR1C1 = "=R[-19]C"
'ETA = Time
    Range("D11").FormulaR1C1 = "=IF(R[22]C[19]="""",MilitaryToTime('Voyage Specifics'!R[-2]C[-1]),MilitaryToTime(R[22]C[19]))"
    Range("D30").FormulaR1C1 = "=R[-19]C"
    Range("D49").FormulaR1C1 = "=R[-19]C"
'ETA = Date
    Range("F11:H11").FormulaR1C1 = "=IF(R[22]C[19]="""",'Voyage Specifics'!R[-1]C[-3],R[22]C[19])"
    Range("F30:H30").FormulaR1C1 = "=R[-19]C"
    Range("F49:H49").FormulaR1C1 = "=R[-19]C"
'Wind = R11
    Range("D12").FormulaR1C1 = "=R[-1]C[14]"
    Range("D31").FormulaR1C1 = "=R[-19]C"
    Range("D50").FormulaR1C1 = "=R[-19]C"
'Wave = R12
    Range("D13,D32,D51").FormulaR1C1 = "=R[-1]C[14]"
    Range("D32").FormulaR1C1 = "=R[-19]C"
    Range("D51").FormulaR1C1 = "=R[-19]C"
'Swell = R13
    Range("D14").FormulaR1C1 = "=R[-1]C[14]"
    Range("D33").FormulaR1C1 = "=R[-19]C"
    Range("D52").FormulaR1C1 = "=R[-19]C"
'Temp = R14
    Range("D15").FormulaR1C1 = "=R[-1]C[14]"
    Range("D34").FormulaR1C1 = "=R[-19]C"
    Range("D53").FormulaR1C1 = "=R[-19]C"
'Pressure = R15
    Range("D16").FormulaR1C1 = "=R[-1]C[14]"
    Range("D35").FormulaR1C1 = "=R[-19]C"
    Range("D54").FormulaR1C1 = "=R[-19]C"
'Officer
    Range("J17").FormulaR1C1 = "=R[-1]C[8]"
    Range("J36").FormulaR1C1 = "=R[-19]C"
    Range("J55").FormulaR1C1 = "=R[-19]C"
'Selected Arrival
    Range("I5:J5").FormulaR1C1 = "=IF('Voyage Specifics'!R[3]C[-6]="""",""No Port Selected"",'Voyage Specifics'!R[3]C[-6])"
    Range("I24:J24").FormulaR1C1 = "=R[-19]"
    Range("I43:J43").FormulaR1C1 = "=R[-19]"
'Voyage #
    Range("N4").FormulaR1C1 = "='Voyage Specifics'!RC[-11]"
    Range("N23").FormulaR1C1 = "=R[-19]"
    Range("N42").FormulaR1C1 = "=R[-19]"
'Course
    Range("N5").FormulaR1C1 = "=R[5]C[4]"
    Range("N24").FormulaR1C1 = "=R[-19]C"
    Range("N43").FormulaR1C1 = "=R[-19]C"
'Speed
    Range("N6").FormulaR1C1 = "=IF((R[22]C[9]=""""),(R[1]C[0]/(R[2]C[-10]+(R[2]C[-8]/60))),R[22]C[9])"
    Range("N25").FormulaR1C1 = "=R[-19]C"
    Range("N44").FormulaR1C1 = "=R[-19]C"
'Trip Distance
    Range("N7").FormulaR1C1 = "=IF(R[22]C[10]="""",R[1]C[0],R[22]C[10])"
    Range("N26").FormulaR1C1 = "=R[-19]C"
    Range("N45").FormulaR1C1 = "=R[-19]C"
'Total Distance
    Range("N8").FormulaR1C1 = "=IF(R[22]C[10]="""",IF(R[12]C[12]=""Yes"",R[3]C-R[2]C[-10],R[1]C[4]+'Voyage Specifics'!R[4]C[-11]),R[22]C[10])"
    Range("N27").FormulaR1C1 = "=R[-19]C"
    Range("N46").FormulaR1C1 = "=R[-19]C"
'TAS
    Range("N9").FormulaR1C1 = "=IF(R[22]C[11]="""",(R[-1]C/(RC[-10]+(RC[-8]/60))),R[22]C[11])"
    Range("N28").FormulaR1C1 = "=R[-19]C"
    Range("N47").FormulaR1C1 = "=R[-19]C"
'Spd Req
    Range("N10").FormulaR1C1 = "=IF(R[22]C[10]="""",(RC[-10]/(((R[1]C[-8]+R[1]C[-10]+(TIME(IF(R[11]C[12]=""Yes"",(VLOOKUP(R[-5]C[-5],Ports!R[-7]C[-12]:R[42]C[-4],8,FALSE))-1,(VLOOKUP(R[-5]C[-5],Ports!R[-7]C[-12]:R[42]C[-4],8,FALSE))),0,0)))-(R[-6]C[-8]+R[-6]C[-10]+(TIME(R[-5]C[-11],0,0))))*24)),R[22]C[10])"
    Range("N29").FormulaR1C1 = "=R[-19]C"
    Range("N48").FormulaR1C1 = "=R[-19]C"
'Voyage Tot Dist
    Range("N11").FormulaR1C1 = "=(IF(R[23]C[11]<>"""",R[23]C[11],'Voyage Specifics'!RC[-11]))"
    Range("N30").FormulaR1C1 = "=R[-19]C"
    Range("N49").FormulaR1C1 = "=R[-19]C"
'Heavy Weather Time
    Range("N12").FormulaR1C1 = "=IF(R[7]C[4]<=(R[-4]C[-10]+R[-4]C[-8]),R[7]C[4],""Error"")"
    If IsNumeric(Range("D8").Value) Then
        If Range("R19").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
            Range("N12").Value = "Error"
        End If
    End If
    Range("N31").FormulaR1C1 = "=R[-19]C"
    Range("N50").FormulaR1C1 = "=R[-19]C"
'Heavy Weather Distance
    Range("N13").FormulaR1C1 = "=R[7]C[4]"
    Range("N32").FormulaR1C1 = "=R[-19]C"
    Range("N51").FormulaR1C1 = "=R[-19]C"
'Slow Steaming Time
    Range("N14").FormulaR1C1 = "=IF(R[9]C[4]<=(R[-6]C[-10]+R[-6]C[-8]),R[9]C[4],""Error"")"
    If IsNumeric(Range("D8").Value) Then
        If Range("R23").Value > (Range("D8").Value + (Range("F8").Value / 60)) Then
            Range("N14").Value = "Error"
        End If
    End If
    Range("N33").FormulaR1C1 = "=R[-19]C"
    Range("N52").FormulaR1C1 = "=R[-19]C"
'Slow Steaming Distance
    Range("N15").FormulaR1C1 = "=R[9]C[4]"
    Range("N34").FormulaR1C1 = "=R[-19]C"
    Range("N53").FormulaR1C1 = "=R[-19]C"


'Voyage Report Indicators
    Range("Z20").FormulaR1C1 = "=IF('Voyage Specifics'!R[-4]C[-20]=""Exact Route Calculator Enabled"",""Yes"",""No"")"
    Range("Z21").FormulaR1C1 = "=IF(AND((Notes!R[-8]C[-14]+Notes!R[-8]C[-13])<((IF(R[-17]C[-20]=""No Data Input"",'Voyage Specifics'!R[-15]C[-23]+'Voyage Specifics'!R[-14]C[-23],R[-17]C[-20]+R[-17]C[-22]))),(Notes!R[-7]C[-14]+Notes!R[-7]C[-13])>((IF(R[-17]C[-20]=""No Data Input"",'Voyage Specifics'!R[-15]C[-23]+'Voyage Specifics'!R[-14]C[-23],R[-17]C[-20]+R[-17]C[-22])))),""Yes"",""No"")"
    Range("Z22").FormulaR1C1 = "=IF('Voyage Specifics'!R[-14]C[-20]=""Loaded"",""L"",""B"")"
   
'Manual Input Clearing
    Range("R5:R16,R19,R20,R23,R24").ClearContents
'Calls Button Macro
  
    Dim w As Worksheet
        Dim a As Button
        Dim b As Button
        Dim c As Button
        Dim d As Button
        Dim e As Button
        Dim f As Button
        Dim g As Button
        Dim h As Button
        Dim j As Button
        Set w = ActiveSheet
   'Print
        Set a = w.Buttons.Add(840, 10, 108, 30)
        a.OnAction = "Print_Sheet"
        a.Characters.Text = "Print"
    'Save
        Set b = w.Buttons.Add(840, 50, 108, 30)
        b.OnAction = "Save_As"
        b.Characters.Text = "Save & Quit"
    'Modify Voyage Specifics
        Set c = w.Buttons.Add(840, 90, 108, 30)
        c.OnAction = "Modify_Voyage"
        c.Characters.Text = "Modify Voyage Specifics"
    'Reset Sheet
        Set d = w.Buttons.Add(840, 130, 108, 30)
        d.OnAction = "NoonDel"
        d.Characters.Text = "Reset Sheet"
    'Recalculate Sheet
        Set e = w.Buttons.Add(840, 170, 108, 30)
        e.OnAction = "Recalc_Sheet"
        e.Characters.Text = "Recalculate Sheet"
        
    'Manual Inputs
        Set f = w.Buttons.Add(840, 210, 108, 30)
        f.OnAction = "ManualCalcShow"
        f.Characters.Text = "Manual Inputs"
        
    'ETA Calculator
        Set f = w.Buttons.Add(840, 250, 108, 30)
        f.OnAction = "ETA_CalcShow"
        f.Characters.Text = "Show ETA Calculator"
        
    'ETA Calculator Calculate
        Set g = w.Buttons.Add(568, 490, 108, 30)
        g.OnAction = "ETA_CALC1"
        g.Characters.Text = "ETA Calculator 1"
        g.Visible = False
            
    'ETA Calculator Calculate
        Set h = w.Buttons.Add(688, 490, 108, 30)
        h.OnAction = "ETA_CALC2"
        h.Characters.Text = "ETA Calculator 2"
        h.Visible = False


    'Hide ETA/Manual Inputs
        Set j = w.Buttons.Add(825, 515, 150, 30)
        j.OnAction = "ETACALCHIDE"
        j.Characters.Text = "Hide Manual Inputs/ETA Calculators"
        j.Visible = False
        
        
        
'Cell Formats
    Range("D4,D23,D42").NumberFormat = "h:mm;@"
    Range("F4:H4,F23:H23,F42:H42").NumberFormat = "dd-mmm-yy"
    Range("D11,D30,D49").NumberFormat = "h:mm;@"
    Range("F11:H11,F30:H30,F49:H49").NumberFormat = "dd-mmm-yy"
    Range("N6,N25,N44").NumberFormat = "0.0"
    Range("N9,N28,N47").NumberFormat = "0.0"
    Range("N10,N29,N48").NumberFormat = "0.00"
    Range("N11,N30,N49").NumberFormat = "0.0"
    Range("W33").NumberFormat = "@"
    Range("Y33:Z33").NumberFormat = "[$-409]d-mmm-yy;@"
    Range("N5,N24,N43,R10").NumberFormat = "@"
    Range("N5,N24,N43").HorizontalAlignment = xlRight
    Range("R28").NumberFormat = "@"


'Conditional Formatting
    With Cells(20, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""Yes""")
        .Interior.Color = 5287936
        .StopIfTrue = False
    End With
    With Cells(20, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""No""")
        .Interior.Color = vbRed
        .StopIfTrue = False
    End With
    With Cells(21, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""Yes""")
        .Interior.Color = 5287936
        .StopIfTrue = False
    End With
    With Cells(21, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""No""")
        .Interior.Color = 15773696
        .StopIfTrue = False
    End With
    With Cells(22, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""L""")
        .Interior.Color = 5287936
        .StopIfTrue = False
    End With
    With Cells(22, 26).FormatConditions.Add(xlCellValue, xlEqual, "=""B""")
        .Interior.Color = 15773696
        .StopIfTrue = False
    End With
    
'Unlocks Input Cells for Use
    Range( _
        "R5:R16,R19:R20,R23:R24,R28,T28,S29:T29,S31:T31,S32:T32,W24:Z25,W26,W27,Y26,Y27,X28:Y32,W33,Y33:Z33,Y34,S6,S7" _
        ).Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    
'Hides ETA Calculators
    Range("Q27:T27,R28,T28,S29:T29,S31:T31,S32:T32").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("P27:T32").Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Range("Q27:T32").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Locked = True
    Range("R5").Select
    
'Hides Grid Lines
    ActiveWindow.DisplayGridlines = False
    
'Renables the Calculator
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
          
'Zooms to 80%
    ActiveWindow.Zoom = 80
    Range("R5").Select
'Protects Sheet
    ActiveSheet.Protect Password:="1234567890", UserinterFaceOnly:=True
    ActiveSheet.EnableSelection = xlUnlockedCells
End Sub

Rick Rothstein · Dec 10, 2018

sassriverrat said:
So now a new problem arises- I'm getting an error '13' when trying to create a noon sheet. My arrival sheet creator is working perfectly and using your piece but the noon sheet creator bugs out before it can reach the end. Maybe you notice something in here I don't? It's bugging out and highlighting the 'militarytotime = format(replacemiltime' piece.

What value is in the MilTime variable when the error occurs and, if different, what is in the cell that the MilTime variable gets its value from?

Military Time Converting wrong

sassriverrat

Well-known Member

sassriverrat

Well-known Member

Excel Facts

Rick Rothstein

MrExcel MVP

sassriverrat

Well-known Member

sassriverrat

Well-known Member

Rick Rothstein

MrExcel MVP

sassriverrat

Well-known Member

Rick Rothstein

MrExcel MVP

sassriverrat

Well-known Member

sassriverrat

Well-known Member

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Military Time Converting wrong

Well-known Member

Well-known Member

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock