Date -Time not populating correctly

[boccuz]

2 - 2021 tally.xlsm

Hello, I appreciate your help.

In the attached spreadsheet, I have two work sheets. The first worksheet "May Data" is how the data should appear. The data is being pulled from another system. Sometime in June, the application from the other system was updated and the format of the data in column A was changed as it appears in A2 and A3 in the "Jun Data" worksheet. I cannot get the data in Column "E" and "J" in the "Jun Data" worksheet to appear as in the "May Data" worksheet. Any help is greatly appreciated.

Thank you,

[Paul_Hossler]

The May dates were just strings that looked like dates, whereas the June dates were true Excel dates (i.e. a double containing the number whole and fracional days since the Excel epoch, or 1/1/1900).

That's why Left, Mid etc. works in May but not in June

Control-tilde switches the display from the 'pretty mode' to the 'behind the scenes mode', where you can see formulas, raw numbers, full precision, etc. (the screen shots)

May.JPG

June.JPG

Since col A are 'real' Excel dates, number formats for col E and F should do everything



Capture.JPG

Capture2.JPG

[SamT]

Change the Formula in E2 to = "=A2" and copy down.

Change the Shift Function to

Function Shift(D As Range, DayShift As Boolean) As Long
Dim T As String
    T = Format(D, "hh:mm")

    If DayShift Then
        If "06:00" <= T And T < "22:59" Then
            Shift = 1
        Else
            Shift = 0
        End If
    
    Else
        If ("23:00" <= T And T < "23:59") Or ("00:00" <= T) And (T < "06:00") Then
            Shift = 1
        Else
            Shift = 0
        End If
    End If
End Function

You can Format Cells in Column E to only display times.

This won't work for any old Data sheets

You can use a Formula to call this function in Column E of old Data sheets to retrieve the date from Column A

Function GetDate(Dispatched As Range) As Date
    GetDate = Split(Dispatched, " ")(0) & " " & Split(Dispatched, " ")(1)
End Function

E2 = GetDate(A2)

[snb]

or as UDF
"=F_snb(A1,True)"

Function F_snb(D, DayShift)
   F_snb = Abs(Hour(D) = 23 Or D < 0.25)
   If DayShift Then F_snb = Abs(D >= 0.25 And Hour(D) < 23)
End Function

[boccuz]

Thanks everyone for your help. I changed the formula in columns E and J to "=A2". However, the coded in Columns F, G, H and K are not populating the results.

[arcarc1309]

To get around your May to June string vs excel dates issue, try changing Formula in E2 to =TEXT(A2,"hh:mm") and then copy down.

[SamT]

Function Shift(D As Range, DayShift As Boolean) As Long worked when I tested it

Have you tried snb's function and formula? It also goes in mod_Shift.

I noticed that all the formulas in the columns were CSE (Array) formulas. That gave me some issues.
Select H2 and press Enter. Repeat for G2 and H2. That should change the CSE formulas to ordinary formulas. Then copy F2:H2 down

Note that I had to remove (un check) a missing reference to get the function to work at all

[Paul_Hossler]

Since June Col A has Excel-friendly dates, you could simplify and get rid of Cols E, J, and K


Here's a function Shift1 that seems to work

Option Explicit


Function Shift1(T As Date, DayShift As Boolean) As Long
    Select Case T - Int(T)
        Case TimeSerial(6, 0, 0) To TimeSerial(22, 59, 0)
            Shift1 = IIf(DayShift, 1, 0)
        Case TimeSerial(23, 0, 0) To TimeSerial(23, 59, 0)
            Shift1 = IIf(DayShift, 0, 1)
        Case TimeSerial(0, 0, 0) To TimeSerial(6, 0, 0)
            Shift1 = IIf(DayShift, 0, 1)
    End Select
End Function

Capture.JPG


Personally, I'd make a macro to reformat May worksheet's Col A into Excel-friendly dates and use the same logic on all sheets

'         1111111
'1234567890123456
'05/01/2021 07:05 2021-05-01 07:05:13.0


Sub FixOldData()
    Dim r As Range, r1 As Range
    Dim i As Long, y As Long, m As Long, d As Long, h As Long, n As Long
    
    With ActiveSheet
        Set r = .Cells(2, 1)
        Set r = Range(r, r.End(xlDown))
    End With


    For Each r1 In r.Cells
        With r1
            y = Mid(.Value, 7, 4)
            m = Left(.Value, 2)
            d = Mid(.Value, 4, 2)
            h = Mid(.Value, 12, 2)
            n = Mid(.Value, 15, 2)
        
            .Value = DateSerial(y, m, d) + TimeSerial(h, n, 0)
        End With
    Next
End Sub

[boccuz]

I want to thank everyone for your help. Paul, your VBA code works great. I appreciate you placing in the workbook. I was able to copy and paste it to my working file.