Results 1 to 4 of 4

Thread: Formula with combine date and time

  1. #1
    Neophyte krishak77's Avatar
    Join Date
    Jun 2021
    Posts
    4
    Articles
    0
    Excel Version
    2008

    Formula with combine date and time



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello All,

    I would really appreciate if someone could help me on below problem.

    I have a data which contains in one of the column with date and timings as per below format.
    6/16/2021 13:43

    on the data it will contains yesterday's and today's, so i want to remove the data which is yesterday's before 9.30pm and again yesterday's data should be only which is after 9.30pm.

    is there any formula which can map Before and After

    Case Number Date
    12367912-T0C1K7 6/20/2021 13:01
    12331119-C9M4H7 6/20/2021 13:01
    12331731-M0K7G9 6/20/2021 17:01
    12331723-G4F7B5 6/20/2021 18:01
    12331726-Y7N5D6 6/20/2021 20:01
    12331727-J2V4Y5 6/20/2021 21:01
    12332913-K0T4S1 6/20/2021 22:01
    12333067-P8R5H8 6/20/2021 23:01

    From the above table next to date column for the first 6 rows should map as Before and last 2 rows should map as After.

    Thank you in advance.

    Regards,
    Anantha

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    328
    Articles
    0
    Excel Version
    2019
    A VBA solution:

    Code:
    Option Explicit
    
    
    Sub BForeAftr()
        Dim lr As Long, i As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        For i = 2 To lr
            If Range("C" & i) > Date - 1 + 0.89583 Then
                Range("D" & i) = "After"
            Else: Range("D" & i) = "Before"
            End If
        Next i
    End Sub

  3. #3
    Neophyte krishak77's Avatar
    Join Date
    Jun 2021
    Posts
    4
    Articles
    0
    Excel Version
    2008

    Excel Formula

    Hello Alansidman,

    Thank you for your reply and solution, however i don't want to use coding and run macro, is there any formula to give on cell, next to date column which can map Before and After based on Date and Timings.

    Regards,
    Anantha

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    328
    Articles
    0
    Excel Version
    2019
    In C3 =IF(B3<INT(TODAY())-1+0.89583,"Before","After") and copy down.

Tags for this Thread

Posting Permissions

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