Change Date column if new row is added after hours

MHalvey

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

Paul Newcome

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

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))

Paul Newcome

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.

Paul Newcome

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)

MHalvey

@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

Paul Newcome

Happy to help. 👍️

MHalvey

@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

Paul Newcome

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))

MHalvey

Excellent! Worked like a charm, thank you again! Have a wonderful rest of your day! 😁

Paul Newcome

Happy to help. 👍️

MHalvey

@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

MHalvey

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

Paul Newcome

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.