DATE FROM ANOTHER COLUMN GOES TO THE NEXT FRIDAY

Travis Mickel

I want to use the date from a column and when there is a date in that column to make the date in the next column the friday in the next week.

Column A - Arrives at Port 7/1/20 (This is the date column A actually is)

Column B - Inventory Received 7/10/20 (This is the date I want column B to reflect in real time from a formula)

Column B= The Friday of the following week based on the date in Column A (I do not want it to be the Friday in the current week.

Is there a formular or a way to create this in Smart Sheets

Any help would be greatly apprecaited

Brian Campbell

Hello Travis,

This is one thing I though of. Create a column for what day of the week arrives at port using the weekday formula of "Arrives at Port". Also create a table ("Day of Week" and "Days to Next Friday") based off the day of the week and how many days till the next Friday. For your example something shipped on Wednesday (Day of week = 4) would be 9 days away from the following Friday. Finally, in the "Inventory Received" column (see image below), have the formula be to add the "Arrives at Port" date to a vlookup that will give us the number to add to the arrives at port date to get us the following Friday's date. I believe this should work, I know there is a lot going on so if you need me to explain anything in more details just let me know.

Michael

Travis Mickel

Michael this is much appreciated. I am doing this based on a smart sheet that is already created. The arrives at port and inventory received are in a much larger smart sheet how would I be able to input this into an existing smart sheet sot hat it exists but is not visible to others looking at the spreadsheet.

Brian Campbell

Travis,

Could you create a copy of the original sheet and have any new rows get moved to the copy as they come by. Then use a vlookup of the Arrives at port day into this copy sheet to pull the new date into the Main larger sheet under inventory sheet. This way you see it in the background but they will only see the formula for the Inventory Received column.