Formula with combine date and time

K

krishak77

New member
Joined
Jun 21, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2008
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
 
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
 
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
 
You must log in or register to reply here.
Back
Top