Automatically update date within a formula

DanW3487

New Member
Joined
Apr 13, 2017
Messages
9
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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
It might help if you post some sample data from your table. You could use something like this perhaps:


Excel 2016 (Windows) 32 bit
ABCDEFG
1Friday:14/04/2017
2
3FridaySaturdaySundayMondayTuesdayWednesdayThursday
442839428404284142842428434284442845
Sheet1
Cell Formulas
RangeFormula
A4=TEXT($B$1+COLUMN()-1,"0")


WBD
 
Upvote 0
Apologies I still do not understand, what you have given me is helpful.

But I am trying to automatically update a "number" within a formula (example 1 in original post) How do I input within the formula or sheet to change a certain part of a formula.
Example: =COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,"42838") - I need to automatically update the number in red with the new dates?
 
Upvote 0
You can use the =TODAY() function in any cell in your workbook - it will always return today's date (i.e. will update each time that date changes). So if in your formula you want a date that was a seek ago, replace your hard-coded date with this: TODAY()-7
 
Upvote 0
Thanks, but the date within the formula is a criteria match for another spreadsheet. So how do I get that "42832" to automatically change using some sort of formula/function? I understand how to use the =today function, I just dont know how to change the criteria "" number that is gathering data from another spreadsheet?

I tried to use cell ref within the "" marks ("A47") but that didnt work, as its looking for that specific number/letter.
 
Upvote 0
You can reference any cell with a date in it with a simple cell reference: A1

If it's on another sheet, then: 'Sheet Two'!A1

If that doesn't answer your query, then you are going to need to give us some sample data so we can see the layout and what exactly you are trying to reference.
 
Upvote 0
Ok. I'm assuming the formula parameter *must* be text and not a number. If you're reporting Friday to Thursday then first we need to work out when was last Friday. We can do this using WEEKDAY. Put the following in a cell in Excel and it will go back to the previous Friday:

Code:
=TODAY()-WEEKDAY(TODAY(),15)+1

So in your formula you could use the following to get the results for last Friday:

Code:
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+1,"0"))

You can then get details for other previous days by progressively adding one to the formula for Sat/Sun/Mon/Tue/Wed/Thu:

Code:
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+2,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+3,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+4,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+5,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+6,"0"))
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(TODAY()-WEEKDAY(TODAY(),15)+7,"0"))

WBD
 
Upvote 0
If you want to reference the date that's in A47 then:

Code:
=COUNTIFS('Sales Report'!$D:$D,"upgrade paperwork sent",'Sales Report'!$K:$K,"Mrs Brown",'Sales Report'!$G:$G,TEXT(A47,"0"))

WBD
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top