How to Retrieve Today's and Yesterday's Data in Excel
Question details
The user needs a method to dynamically extract specific data (such as daily building readings) for today and yesterday from columns with changing date headers, without manually updating the formula references.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking daily metrics where new data is added across columns labeled with consecutive dates, and requiring an automated way to query the most recent two days of data.
- Observed behavior
- The goal is to implement a dynamic formula setup that identifies today's date and yesterday's date within the table headers and successfully retrieves the corresponding column values.
Before applying dynamic date formulas, ensure your column headers are formatted as valid Excel date values rather than plain text strings, as text formats can cause mathematical date evaluations to fail.
Use the FILTER Function with TODAY()
The most efficient way to pull columns dynamically based on the current date in modern Excel is by using the FILTER function paired with TODAY().
The TODAY() function automatically updates to the current system date. By using TODAY()-1, you can accurately reference yesterday. Combining this logic with the FILTER function allows you to extract entire columns whose headers fall within this rolling two-day window.
Ensure your dataset is structured properly and ideally formatted as an Excel Table (Ctrl+T) so the formula can reference structural elements like [#Headers].
Select an empty cell where you want the extracted data to spill. Type the formula: =FILTER(full_table, (full_table[#Headers]>=TODAY()-1) * (full_table[#Headers]<=TODAY()))
If your date headers are stored as text and the formula fails, convert them dynamically within the formula using a double unary operator: IFERROR(--full_table[#Headers],0).
Use WORKDAY for Business Day Data
If your data tracking is strictly limited to business days, subtracting one from TODAY() will return empty data on Mondays.
Restructure Data into Rows for Better Long-term Use
Continuously adding new date columns horizontally is not optimal for spreadsheet performance or long-term data analysis.
Dynamically Analyze Daily Data in WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas, TODAY(), and WORKDAY() functions. You can easily build automated daily reports without compatibility issues or missing features.
- 1. Open Your Dataset: Launch WPS Spreadsheet and open your existing data tracking file.
- 2. Apply the TODAY Function: Select the cell for your data output and utilize functions like TODAY() and WORKDAY() inside your lookup formulas.
- 3. Save in Excel Format: Save your document in the standard .xlsx format to ensure your dynamic date formulas remain perfectly compatible across different software.
Frequently Asked Questions
Why does my formula using TODAY()-1 return an error or no data?
This usually occurs if your column headers are formatted as text rather than valid Excel date serial numbers. Ensure the headers are formatted as Dates. If they must remain text, use the value conversion trick by adding '--' before the cell reference in your formula, like --full_table[#Headers].
How do I skip weekends when pulling yesterday's data?
Instead of subtracting 1 from TODAY(), use the formula WORKDAY(TODAY(), -1). This will automatically bypass Saturdays and Sundays. For example, if today is Monday, WORKDAY(TODAY(), -1) will return the date for the previous Friday.
Is it better to track daily data in columns or rows?
It is highly recommended to track daily data vertically in rows rather than horizontally across columns. Storing dates in a single 'Date' column and adding new records downwards makes it significantly easier to use PivotTables, SUMIFS, and dynamic filtering.
How can I extract numbers without the degree symbol affecting calculations?
You should only enter raw numeric values into your cells. To display the degree symbol, highlight the cells, right-click, select 'Format Cells', go to 'Custom', and enter '0°' or '0.0°'. This keeps the underlying data purely numeric, preventing calculation errors in your formulas.
