Created Date Time Cutoff

Paul Osenenko

I'm using a created date column "Order Date" and a dropdown column "Expedited Options" in a formula to product an automatic "Guaranteed Completion Data". The formula works, but I notice that if the order date timestamp is after a certain time it defaults to the next day and adds a day to my completion date column. I know that in the 4PM and 5PM hours it stays with the current day, and in the 9PM hour it goes to the next (adds a day to completion). Does anyone know what that cutoff time is, and if it is adjustable? 

Paul Newcome

It is based on your time zone. You would need to ad an IF statement to strip out the hour and "PM" portions to say that if it is greater than that particular hour and "PM" then subtract 1 from the date.

Are you able to provide a screenshot of your created date column as well as the formula you are currently using?

Paul Osenenko

Hi Paul, Thank you for the response. Below are the screenshots as requested. I honestly like the way it is working now where at some point it considers the entry based on the time stamp the next day. I just don't necessarily know what time that cutoff is so that I can say " Any orders submitted after 5PM will be considered the next day". The best outcome would be if there is a way I can set it for 3PM or whatever time our expediters stop processing orders for the day.

Thanks,

Paul Osenenko

Column Formula for the "Guaranteed Completion Date" column:

=IF([EXPIDITED ORDER OPTIONS]@row = "15 Day Rush", [ORDER DATE]@row + 15, IF([EXPIDITED ORDER OPTIONS]@row = "10 Day Rush", [ORDER DATE]@row + 10, IF([EXPIDITED ORDER OPTIONS]@row = "3 Day Rush", [ORDER DATE]@row + 3, IF([EXPIDITED ORDER OPTIONS]@row = "Next Day (Call for Mgr. Approval)", [ORDER DATE]@row + 1, [ORDER DATE]@row + 30))))

Paul Newcome

If you want it for any time after 3PM (modification instructions to follow), you could use something like this in a helper column to establish the order date whether it be same day or next day based on time.

=DATEONLY([Order Date]@row) + IF(AND(FIND("PM", [Order Date]@row)> 0, VALUE(SUBSTITUTE(MID([Order Date]@row, FIND(" ", [Order Date]@row) + 1, 2), ":", ""))>= 3, VALUE(SUBSTITUTE(MID([Order Date]@row, FIND(" ", [Order Date]@row) + 1, 2), ":", "")) <> 12), 1, 0)

If you wanted it to push to the next day for anything after 4PM then you would change the bold 3 to a 4. I would suggest first finding out which hour it changes over at though. If needed, you can create a second sheet and submit a generic form with the Created Date type column in place a few minutes past each hour. Since it will be the same across all sheets, you don't need to worry about messing up your active sheet with a bunch of tests.

What is your time zone? We should be able to figure out the cutoff by comparing your time zone to GMT.