Setting date based upon another date

Markalexo

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".

Paul Newcome

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)

Markalexo

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)

Paul Newcome

Ok. Lets try this... Replace the formula in the Date Helper column with this formula:

=SUBSTITUTE(Created@row, LEFT(Created@row, FIND(" ", Created@row)), "")