# Thread: Formula with combine date and time

1. ## 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. 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. ## 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. In C3 =IF(B3<INT(TODAY())-1+0.89583,"Before","After") and copy down.

#### Posting Permissions

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