0

Is it possible to filter one of two Excel pivot tables to show the last 24 hours data from the current date/time?

I'm working with an Excel spreadsheet that has a series of dates as one of the columns, I've created two pivot tables from this data, one to show all the items since the start of the week, and another table that I'm trying to show the data for the last 24 hours from the current date/time. The simple function =NOW()-1 will give me the date and time 24 hours ago, but I cant find any way to filter the pivot table using this function.

I can filter by using the inbuilt date filters, but that only has set values from/to/today/yesterday/month/quarter/etc - just about every date range apart from 24 hours. The 'custom filter' only accepts a fixed date as the filter and wont accept a formula.

Sheet one has the data extracted from SQL containing all the records for the last week. Sheet two has two pivot tables based on this data, one showing all the records for the last week, and the other I'm trying to filter to show data for the last 24 hours from the current time.

I must be missing how to do this but cant find any way to filter by a dynamic date and would be grateful for help.

user2871153
  • 9
  • 1
  • 2
  • 1
    Are you able to add `=NOW()-1` formula column to your original data source? –  Oct 11 '13 at 14:40
  • 1
    @nethy, I'm with you. I'd generally rather modify the source to make pivot tables work. My only hesitation is `NOW` being volatile. I suppose it could also me a static cell with the current date/time that gets modified when the workbook is opened. – Doug Glancy Oct 11 '13 at 15:22
  • 1
    Thanks very much nethy! Yes thats a great solution, I can add an extra column to the data source =IF(cell – user2871153 Oct 11 '13 at 15:40
  • Thanks for the suggestion Doug, that is what I've done to make the pivot table work the way I wanted. – user2871153 Oct 11 '13 at 15:45
  • I'm glad you got what you needed. – Doug Glancy Oct 11 '13 at 15:50

0 Answers0