Copy and keep cell value based on day of the week.

Jerome Thomas

Hello All,

Is there a formula that would take a value from cell "A", copy it to cell "B", and keep that copied value in cell "B" until a set number of days have passed?

Backstory: I currently have a dashboard with graphs that illustrates a rolling 7-day, 30-day, 60-day, and 90-day account interactions. I would like to compare the current 7/30/60/90-day values to their previous 7/30/60/90-day values so that I can see if utilization has went up or down in their respected time frame. I have a sheet with a "Date" cell that I update every M-F, so my logic was to create a formula based on the day of the week (Every Monday) that would drive my trigger for the copy & paste need. I've tried =If(Contains("Monday" but Smartsheets has a default date format "5/8/20" that the "Contains(" function relays on even if you change the date format to match below. I've tried other formulas but can't seem to piece it together to get the result I need. Any assistance would be greatly appreciated.

Sean Morgan

Hello @Jerome Thomas

I was able to create a formula that may replicate what you are looking for, see this below:

As you can see, I have a Date Column as well as a Column A and Column B. The Column A cell is where you would put the value, and the Column B cell is where the formula is inserted. The Formula I used is =IF(Date1 = "05/09/20", "", A3). This reads that if the Date cell is = to "05/09/20", to enter "" which make the cell in Column B a blank cell, otherwise, insert the value in the Column A cell. The two screenshots below show what occurs when the date is "05/09/20" and what occurs when it's not:

 Not meeting criteria

Meeting Criteria

Please let me know if you have any questions

Kindest Regards

Sean

Paul Newcome

How are you calculating the data in Cell A?

Jerome Thomas

Paul,

I'm using a countifs function that has a 7 day date range.

=COUNTIFS({Range on 2nd sheet}, "Account", {Salesforce Telemed Data - Excel Imports Range 3}, <=TODAY(7), {Salesforce Telemed Data - Excel Imports Range 3}, >=TODAY(-7)).

Jerome Thomas

Hello,

@Sean Morgan Does (Date = "") update automatically or is that a manual process?

Paul Newcome

So you have a two week period? Last seven days plus next seven days? Try something like this to show the previous week's range...

=COUNTIFS({Range on 2nd sheet}, "Account", {Salesforce Telemed Data - Excel Imports Range 3}, <=TODAY(), {Salesforce Telemed Data - Excel Imports Range 3}, >=TODAY(-14))