I found a solution, with the use of helper columns
<
Range("AD4:AD" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=IF(RC[-28]="""",RC[-29],"""")"
Range("AD4:AD" & Cells(Rows.Count, 1).End(xlUp).Row).Value = Range("AD4:AD" & Cells(Rows.Count, 1).End(xlUp).Row).Value
Range("AD4:AD" & Cells(Rows.Count, 1).End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Selection.NumberFormat = "m/d/yy h:mm;@"
Range("AE5:AE" & Cells(Rows.Count, 1).End(xlUp).Row).Value = Range("A5:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
Range("AE5:AE" & Cells(Rows.Count, 30).End(xlUp).Row).Replace What:="AM", Replacement:=""
Range("AE5:AE" & Cells(Rows.Count, 30).End(xlUp).Row).Replace What:="PM", Replacement:=""
Range("AF5:AF" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("AF5:AF" & Cells(Rows.Count, 1).End(xlUp).Row).Value = Range("AF5:AF" & Cells(Rows.Count, 1).End(xlUp).Row).Value>
Any ways to make more efficient?