Determine if Created Date is after 12:00 PM

Options

I'm calculating turn-around time for requests. I'm looking for something like the following.

=IF([Created Date] >12:00 PM, DATEONLY([Created Date]@row)+1, DATEONLY([Created Date]@row))

So if a request was submitted 1/19/23 after 12:00 PM I want to add 1 business day to the date. After that I'll calculate my turn-around time based on the new created date and the completion date.

Best Answer

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Brittany S

    If noon is included (ie, If [Created Date]@row>=12:00 PM), a straight forward solution is

    =IF(RIGHT(Created@row, 2) = "PM", Created@row + 1, Created@row)

    Will this work for you?

    Kelly

  • Brittany S
    Options

    Thank you for the idea. I hadn't thought to use just the AM/PM. Below is my completed formula.

    The formula does the following:

    -Returns blank if Completed Date is blank

    -Returns 0.5 (days) if the Created Date and Completed Date are the same

    -Adds one business day to the Created Date if the Created Date contains "PM" (includes holiday table)

    -I'll call that the Date Submitted

    -Returns 0.5 (days) if the Date Submitted and Completed Date are the same

    -If none of the above is true then it returns the number of business days between the Submitted Date and Completed Date (includes holiday table)

    =IF([Completed Date]@row = "", "", IF(DATEONLY([Created Date]@row) = [Completed Date]@row, 0.5, IF(IF(RIGHT([Created Date]@row, 2) = "PM", WORKDAY([Created Date]@row, 1, {Holiday Table Range 1}), [Created Date]@row) = [Completed Date]@row, 0.5, NETWORKDAYS(IF(RIGHT([Created Date]@row, 2) = "PM", [Created Date]@row + 1, [Created Date]@row), ([Completed Date]@row - 1), {Holiday Table Range 1}))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!