Date -Time not populating correctly

[boccuz]

28742

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)

28743

28744

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



28750

28749

[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





28756


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.