Coverting 4 digit/time Julian date to Calendar date/time using VBA.

[Livin404]

Greetings,

I have a Julian Date of 1326/2230 which I would need to be dispalyed as 22 Nov 2021 2230 and another example 1326/0025 which would be displayed as 22 Nov 2021 0025.
I'm thinkingI will have to use some sort of text to columns which will separate the date and time while droping the "/". Then I'll have to insert the first digit in front of the four numbes. After I get 1326 sepaeated I can add a 2 for all dates.

Sub test()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "F").End(xlUp).Row
For Each cell In Range("F1:F" & lastrow)
cell.Value = "2" & cell.Value
Next

End Sub

I do know

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3))

will get me the Calendar date from a 5 digit Julian Date , but I need this in a VBA for Column G.

I need someway of Concating the date and time- bear in mine I need to have the 24 clock. Soon as I remove the/ I loose all leading zeros.

Thank you,

 

[johnnyL]

You can't have the formula bar display 11/22/2021 and the cell display 22 Nov 2021. You can't format it two different ways, that I am aware of.

You can have 11/22/2021 5:30:00 PM OR 22 Nov 2021 5:30:00 PM ... not both.

 

[Livin404]

Good afternoon, thank you for getting back, as you can see and I have two different formats of the date. I have it set to CUSTOM "dd mmm yyyy hhmm".
I'm trying to insert rows for missing dates and I hope to achieve that by using the Macro described in the earlier post.

Very much appreciated.

 

[johnnyL]

Sorry, I was thinking you had text strings.

Sub CopyJulianUDFFormulas_Calculate_DeleteJulianFormulas_DeleteOriginalJulianDataColumn()
'
    Range("G1").Formula = "= julian(F1)"                                                        ' Copy formula to cell
    Range("G1").AutoFill Destination:=Range("G1:G" & Range("F" & Rows.Count).End(xlUp).Row)     ' Copy Formula down the range
'
    With Range("G1:G" & Range("F" & Rows.Count).End(xlUp).Row)                                  ' Loop through the formula column
        .Value = .Value                                                                         '   Remove formulas from cell leaving just the value
        .NumberFormat = "d mmm yyyy h:mm:ss AM/PM"
    End With
'
    Range("F:F").EntireColumn.Delete                                                            ' Delete source column F which is no longer needed
End Sub

 

[Livin404]

Sorry, I was thinking you had text strings.

Sub CopyJulianUDFFormulas_Calculate_DeleteJulianFormulas_DeleteOriginalJulianDataColumn()
'
    Range("G1").Formula = "= julian(F1)"                                                        ' Copy formula to cell
    Range("G1").AutoFill Destination:=Range("G1:G" & Range("F" & Rows.Count).End(xlUp).Row)     ' Copy Formula down the range
'
    With Range("G1:G" & Range("F" & Rows.Count).End(xlUp).Row)                                  ' Loop through the formula column
        .Value = .Value                                                                         '   Remove formulas from cell leaving just the value
        .NumberFormat = "d mmm yyyy h:mm:ss AM/PM"
    End With
'
    Range("F:F").EntireColumn.Delete                                                            ' Delete source column F which is no longer needed
End Sub

Hello, thank you for your continued assistance. I did try that, and I'm afraid now I'm getting the 12 hr time in the cells. In addition when I used the next formula to insert a row for missed dates it only errors out. The format which I provided a picture for on Friday 3:49 PM is what I'm looking for.

Again here is the macro that I thought would work to insert a missing row for rows where there is no date. Can you think of a better way to insert missing dates. With your help I can get my column into a custom format us "dd mmm yyyy hhmm"? Thank you very much.

Sub InsertMissingDates()
   Dim x As Long, diff As Long

Dim LastRow As Long

Dim StartRow As Long

If Int(Cells(1, "E")) <> Date Then

        Rows(1).Insert
        Cells(1, "E").Value = Date
        Cells(1, "C").Value = "N/A"
        Cells(1, "B").Value = "N/A"
        Cells(1, "A").Value = "NO DEPARTURES"

End If
StartRow = 2
LastRow = Cells(Rows.Count, "E").End(xlUp).Row
For x = LastRow To StartRow Step -1
diff = DateDiff("E", Cells(x - 1, "E"), Cells(x, "E"))
    If diff > 1 Then
       Rows(x).Insert
       Cells(x, "E").Value = Int(Cells(x + 1, "E")) - 1
       Cells(x, "C").Value = "N/A"
       Cells(x, "B").Value = "N/A"
       Cells(x, "A").Value = "NO DEPARTURES"
       x = x + 1
    End If
Next x
Cells(1, "E").EntireColumn.NumberFormat = "dd mmm yyyy hhmm"
End Sub

 

[johnnyL]

@Livin404 , you are trying to make what should be two different threads into one. This thread started out as a question about 4 digit julian date and time. if you have any more problems with that I will try to assist you on that. I don't pretend to be a moderator here, but I would suggest you start a new thread concerning filling in the missing dates. That would be less confusing, in my opinion. The missing dates code has issues, but like I said, I think it deserves its own thread.

 

[Livin404]

@Livin404 , you are trying to make what should be two different threads into one. This thread started out as a question about 4 digit julian date and time. if you have any more problems with that I will try to assist you on that. I don't pretend to be a moderator here, but I would suggest you start a new thread concerning filling in the missing dates. That would be less confusing, in my opinion. The missing dates code has issues, but like I said, I think it deserves its own thread.

I understand, you have been an enormous help.

 

[johnnyL]

No worries. Let me know when you start the new thread for the 'filling in the missing dates'.

 

[Livin404]

No worries. Let me know when you start the new thread for the 'filling in the missing dates'.

Hello, I ended up using your product. The strange thing was I had a typo in a "Public Function" which of course caused a problem in with the Macro you provided. Thank you,