Setting date based upon another date

Hi everyone,
I am seeking help in setting a date column based upon another date column.
Put simply, I have a "date received" column and a "created" column, where the created column populates automatically based upon the time a submission was made using a form. The "date received" column I have been manually setting, as any work received after close of business (5:00pm) we consider as being received the following day. So I'm seeking help in writing a formula that essentially says "If (created) is greater than 5:00pm and less than 11:59pm, set (date received) to tomorrow. If (created) is earlier than 5:00pm, set (date received) to today".
Answers
-
Try something like this...
=DATEONLY(Created@row) + IF(VALUE(LEFT(SUBSTITUTE(Created@row, DATEONLY(Created@row) + " ", ""), FIND(":", SUBSTITUTE(Created@row, DATEONLY(Created@row) + " ", "")) - 1)) <> 12, VALUE(LEFT(SUBSTITUTE(Created@row, DATEONLY(Created@row) + " ", ""), FIND(":", SUBSTITUTE(Created@row, DATEONLY(Created@row) + " ", "")) - 1)) >= 5, FIND("P", Created@row) > 0), 1)
-
Hi Paul,
This seems to be right on the right track, however I've run into one snag, where the "helper" column you suggested seems to not work properly for any entry later than 4pm. Do you have any suggestions on how to get around this? I basically copied exactly what you suggested, with the "Date Helper" column containing:
=SUBSTITUTE(Created@row, DATEONLY(Created@row), "")
And the "Test Due Date" column containing:
=DATEONLY(Created@row) + IF(AND(VALUE(LEFT([Date Helper]@row, FIND(":", [Date Helper]@row) - 1)) <> 12, VALUE(LEFT([Date Helper]@row, FIND(":", [Date Helper]@row) - 1)) >= 5, FIND("P", [Date Helper]@row) > 0), 1)
-
Ok. Lets try this... Replace the formula in the Date Helper column with this formula:
=SUBSTITUTE(Created@row, LEFT(Created@row, FIND(" ", Created@row)), "")
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 82 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives