Date + 7 Formula, in cells, in a row

Ashley Holbrook

Hello,

I'm hoping someone can provide a quick answer or solution to doing what is such a simple task in excel in Smartsheet. I have read through numerous posts and watched videos to hopefully solve this but I cannot find a clear cut answer.

I understand that Smartsheet dates are best used in a column for formulas. However, I am trying to set up a project "financial tracker" in Smartsheet and other than manually updating every cell to reflect the week start date, I cannot find another solution.

Below is a screenshot and I have highlighted a date section that I am trying to use a simple formula to reflect "date +7". Our projects generally span anywhere from 13 weeks to 1 year+, and manually updating week start dates for longer projects would not be ideal.

In addition to the screenshot above, we also track the Actuals and Delta so the hours/budget can be reallocated to different weeks in the project, etc. So, updating dates on several different charts is what I am hoping to avoid.

Any help or suggestions are greatly appreciated. I'm even open to changing the layout for how we track. However, I have also not been able to find any ideas, templates, posts on how others may have solved this or are using something similar.

Thank you,

Ashley

heyjay

Assuming that the cells are formatted as TEXT and you will not use them as DATE on other formula.

Have [W1]5, as your start date, use the formula below for your W2, then you can drag the formula to the right.

[W2]5 =REPLACE([W1]5, FIND("/", [W1]5, 1) + 1, IF(FIND("/", [W1]5, FIND("/", [W1]5, 1) + 1) - FIND("/", [W1]5, 1) = 2, 1, 2), VALUE(MID([W1]5, FIND("/", [W1]5, 1) + 1, IF(FIND("/", [W1]5, FIND("/", [W1]5, 1) + 1) - FIND("/", [W1]5, 1) = 2, 1, 2))) + 7)

You will encounter an error with this formula for months without 31 and exceeding 31, (1/25/24 next computation will show 1/32/24). That happens, you might need to add the days manually.

You can nest multiple IF statement if its exceeds 31 or 32, but you will have to do that for each month.

NickStaffordPM

Hey Ashley,

Could you please include screenshots of the Smartsheet you are creating? I am unsure if you are speaking about the column titles or if you need a formula in a cell that adds 7 days to a date stated in a different cell.

=[Date}@row+7

Sorry, either provide the information above or wait for another smartsheet user to answer!\

Ashley Holbrook

Hi Nick,

Thank you for the response. I am looking for the latter, "a formula in a cell that adds 7 days to a date stated in a different cell".

Here is a screenshot that includes the column names. Please note that I was playing around and recently added the "Start Date" field to see if that would be better to use in formulas than taking the first date in the "W1" column on row 5 and trying to add 7 days.

Thank you again!

Ashley

heyjay

Assuming that the cells are formatted as TEXT and you will not use them as DATE on other formula.

Have [W1]5, as your start date, use the formula below for your W2, then you can drag the formula to the right.

[W2]5 =REPLACE([W1]5, FIND("/", [W1]5, 1) + 1, IF(FIND("/", [W1]5, FIND("/", [W1]5, 1) + 1) - FIND("/", [W1]5, 1) = 2, 1, 2), VALUE(MID([W1]5, FIND("/", [W1]5, 1) + 1, IF(FIND("/", [W1]5, FIND("/", [W1]5, 1) + 1) - FIND("/", [W1]5, 1) = 2, 1, 2))) + 7)

You will encounter an error with this formula for months without 31 and exceeding 31, (1/25/24 next computation will show 1/32/24). That happens, you might need to add the days manually.

You can nest multiple IF statement if its exceeds 31 or 32, but you will have to do that for each month.

NickStaffordPM

Hey Ashely, give this a try

Column W1 Row 5 Formula: =[Rate]1+7

Column W2 Row 5 Formula: =[W1]5+7

Column W3 Row 5 Formula: =[W2]5+7

Column W4 Row 5 Formula: =[W3]5+7

Column W5 Row 5 Formula: =[W4]5+7

Column W6 Row 5 Formula: =[W5]5+7

heyjay

https://community.smartsheet.com/discussion/comment/446424#Comment_446424

@Ashley Holbrook - try to format those columns as date but toggle off the line that says 'Restrict to date' then use @NickStaffordPM 's formula. Ignore my initial response.

Ashley Holbrook

Thank you @heyjay and @NickStaffordPM! I really appreciate all of your help! The formula that @heyjay provided worked great and just quickly updated where needed.

I was not able to make Nick's formula work. It just kept repeating the same date (Start Date of 7/1/24). Also, for whatever reason when I formatted the columns to dates and toggled off the "Restrict to Date" it would not work as expected. Everything changed to a date format and I could not override it with just a number or text.

Thank you both again!

Ashley