View Full Version : [SOLVED:] Date -Time not populating correctly
boccuz
07-18-2021, 12:02 PM
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
07-18-2021, 05:24 PM
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
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)
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
07-19-2021, 11:34 AM
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
07-19-2021, 12:02 PM
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.
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
07-19-2021, 01:50 PM
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
07-31-2021, 02:15 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.