Consulting

Results 1 to 9 of 9

Thread: Date -Time not populating correctly

  1. #1
    VBAX Regular
    Joined
    Jan 2018
    Posts
    35
    Location

    Date -Time not populating correctly

    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,

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Last edited by Paul_Hossler; 07-18-2021 at 05:58 PM. Reason: Couldn't get the screenshots right :-(
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  5. #5
    VBAX Regular
    Joined
    Jan 2018
    Posts
    35
    Location
    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.

  6. #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.

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    Attached Files Attached Files
    Last edited by Paul_Hossler; 07-19-2021 at 02:06 PM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Regular
    Joined
    Jan 2018
    Posts
    35
    Location
    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.

Posting Permissions

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