Change Date column if new row is added after hours
Hello  We have a simple ticket system and if a ticket request from a form comes in after 4:00pm, we don't start the ticket until the next day.
Case:
Form Entry happens at 2:30pm on 07/27/23. Start Date column should read 07/27/23.
Form Entry happens at 4:30pm on 07/27/23. Start Date column should read 07/28/23.
So we want the Start Date to reflect the next day if the ticket comes in after 4:00pm. Is that possible? Through Automations, Bridge or anything else? I've been scratching my head trying to think of a workaround.
Thank you in advance,
Michael
"Strive for Progress, not perfection."
Best Answers

You would need to leverage the Created Date column and a formula that will evaluate the time and add 1 to the date.
=DATEONLY(Created@row) + IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) < 12), 1, 0)

You would use this instead:
=WORKDAY(DATEONLY(Created@row), IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) < 12), 1, 0))
Answers

You would need to leverage the Created Date column and a formula that will evaluate the time and add 1 to the date.
=DATEONLY(Created@row) + IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) < 12), 1, 0)

@Paul Newcome As usual  you come through! You just made some very happy employees at my company. Thank you so much! I'll be adding this formula to my cheat sheet for sure. Have a wonderful Friday, my friend.
Michael
"Strive for Progress, not perfection."


@Paul Newcome  My team had a follow up situation. If the Created column comes in after 4pm on a Friday, it will push the Start Date to Saturday. Is there a way to use the Workday formula to push that to Monday?
Sadly the Working Days doesn't work as I hoped it would either for this situation as it's primarily for Gantt charts, I believe.
Thanks again for your help,
Michael
"Strive for Progress, not perfection."

You would use this instead:
=WORKDAY(DATEONLY(Created@row), IF(AND(FIND("P", Created@row)> 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) < 12), 1, 0))

Excellent! Worked like a charm, thank you again! Have a wonderful rest of your day! 😁
"Strive for Progress, not perfection."


@Paul Newcome  We had an odd error show up and I wanted to bounce this off you. Using the formula it seems that if a row is Created after 7:00pm it adds 2 days to the Start Date column instead of just 1 day.
In the image below the bottom row was added 11:73am so shows same day, then the 6:26pm was pushed to next day (both as intended) but then the next 3 moving up all got pushed out 2 days? Any idea why after 7:00pm all entries push 2 days? Is there a system roll overtime with the formula or something?
I appreciate your help with this,
Michael
"Strive for Progress, not perfection."

I believe I created an IF workaround. Looking first if the Created@row is past "7pm", then subtract 1 day. IF not, then apply the original formula. After some more community searching, I found your post about time zones and adjusting the formula for that after 7pm. After testing this it seems to be working so far and I was able to include my Holiday Observed sheet as well.
=IF(AND(FIND("P", Created@row) > 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) >= 7, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) < 12), WORKDAY(DATEONLY(Created@row)  1, IF(AND(FIND("P", Created@row) > 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) < 12), 1, 0), {Holiday_Observed}), WORKDAY(DATEONLY(Created@row), IF(AND(FIND("P", Created@row) > 0, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) >= 4, VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row)  (FIND(" ", Created@row) + 1))) < 12), 1, 0), {Holiday_Observed}))
Thanks again for your help with all this, means a lot!
Michael
"Strive for Progress, not perfection."

That does seem odd, but I'm glad you were able to find a solution. Looks like you also grabbed one that uses the WORKDAY function to skip weekends. I love that function.
Help Article Resources
Categories
Check out the Formula Handbook template!