Most valuable professional
  • Hot Topics

Excel Automatically update date within a formula

Hi, I have a sales table which shows how many sales have been made using a formula that Counts data from another report. Example: =COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,"42838") 42838 being the raw data for the date I want to use. I have a table that shows last Friday - Thursday how many "upgrade paperwork sent" by a certain agent. I am trying to get the report to automatically change the raw date data within the formula to the previous weeks dates. For example: 2 weeks ago, the date was "42825" (Friday) - "42831" (Thursday) (The table has each day Fri,Mon,Tue,Wed,Thu) and now when I go to the report today to get the data for "42832" - "42838" I have to manually change the raw date for each cell Fri-Thu. Is there any way I can get this to automatically change if I input what 'todays' raw date is somewhere on the sheet? P.S I have to use the raw date because the data i gather from can only use this format.

This question generated 15 answers. To proceed to the answers, click here.

This thread is current as of April 13, 2017.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel