Formula for cutoff date

krishak77

New member
Joined
Jun 21, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2008
Hello All,


I need your support for the below criteria.


I have a data in Excel and which need to mention "Before Cutoff, After Cutoff and Present" on the next to date column.


Date which has yesterday before 5.30pm Should mention as "Before Cutoff" and today's date which has date after 5.30pm Should mention as "After Cutoff"


Data should mention as "Present" from yesterday after 5.30pm to today before 5.30pm, and date should calculate only working days.


Below table with reference.


Cutoff Date

Cutoff
Date
Before Cutoff
7/23/2021 16:28
Before Cutoff
7/23/2021 17:28
Present
7/23/2021 17:30
Present
7/23/2021 20:31
Present
7/26/2021 16:31
After Cutoff
7/26/2021 20:31




I need Formula as per above criteria.


Thank you in Advance.


Regards,
Anantha
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,786
Reaction score
1
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
Try this:

=IF(E3<(TODAY()-0.270833),"Before Cutoff",IF(E3>(TODAY()+0.729167),"After Cutoff","Cutoff"))

where E3 is the first cell in the range containing dates and times. Copy down.

Excel 365 (Windows) 32 bit
D
E
3
Before Cutoff
24/07/2021 16:52​
4
Before Cutoff
25/07/2021 04:52​
5
Before Cutoff
25/07/2021 16:52​
6
Cutoff
26/07/2021 04:52​
7
Cutoff
26/07/2021 16:52​
8
After Cutoff
27/07/2021 04:52​
9
After Cutoff
27/07/2021 16:52​
10
After Cutoff
28/07/2021 04:52​
11
After Cutoff
28/07/2021 16:52​
12
After Cutoff
29/07/2021 04:52​
Sheet: Sheet1
 

krishak77

New member
Joined
Jun 21, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2008
Hello AliGW,

Thanks for your reply,

But thing is date should calculate only working days, Eg:- if i calculate on Monday, Friday's Data which is after 5.30pm it should show as Cutoff not before Cutoff.

Is it possible to get formula as per above criteria.

Thank you in Advance.

Regards,
Anantha
 
Last edited by a moderator:

krishak77

New member
Joined
Jun 21, 2021
Messages
6
Reaction score
0
Points
0
Excel Version(s)
2008
Hello All,

Can anyone help on formula for the above criteria.

thank you in advance.

Regards,
Anantha
 
Top