Results 1 to 5 of 5

Thread: Formula for cutoff date

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

    Formula for cutoff date



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

    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

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,710
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    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
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Seeker krishak77's Avatar
    Join Date
    Jun 2021
    Posts
    6
    Articles
    0
    Excel Version
    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 AliGW; 2021-07-27 at 10:22 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,710
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    OK. Sorry, can't help any further.
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Seeker krishak77's Avatar
    Join Date
    Jun 2021
    Posts
    6
    Articles
    0
    Excel Version
    2008
    Hello All,

    Can anyone help on formula for the above criteria.

    thank you in advance.

    Regards,
    Anantha

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
  •