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

Find more posts tagged with

API and Developers

Formulas

Integrations

Automations

Bridge

Accepted answers

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.

All comments

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.

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:

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.