# Thread: Date -Time not populating correctly

1. ## Date -Time not populating correctly

2 - 2021 tally.xlsm

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,  Reply With Quote

2. 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  Reply With Quote

3. 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)  Reply With Quote

4. 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```  Reply With Quote

5. 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.  Reply With Quote

6. 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.  Reply With Quote

7. 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  Reply With Quote

8. 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```  Reply With Quote

9. 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.  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•