Convert formulas to values based on dates

[Trav66]

Hi all,

I would like to take the formulas in a cell and convert that to a value based upon the current date - meaning everything older than today is just a number, not a formula.

Background: This is a productivity report where completed tasks and hours are manually entered at the end of business and then run through a bunch of pivot tables. The dashboard page does a bunch of GETPIVOTDATA and everything is working like it should.

However, we have a separate SQL that gives me real-time data and this is the data that I would like to see in the dashboard for today (the report doesn’t show me older than today or else the update would simply take too long and bog down the server.

So what I need is a macro that will compare dates and anything older than today will be converted from a formula to a number.

Dates are in row 1
Data is in rows 2- 50

In order to make it easy I've attached a dummy file with just one sheet and very basic formulas. It's the principle I'm interested in.

I’ve tried to do the magic myself, but just can’t get it done and none of the macros I’ve found do what I want.
I would appreciate help here.

Cheers

[alansidman]

What does this mean?

So what I need is a macro that will compare dates and anything older than today will be converted from a formula to a number.

Give us an example of the number you would like. Any number? or a specific number?

[jomili]

I think this will do the trick.

Please Login or Register  to view this content.

I changed the value in D1 to 9/20/16 so it had some older dates to work with. It worked fine on your data, converting all values from Rows 2:50 in that column to values.

[Trav66]

Hi jomili,

thanks for the help. That does the trick.

Could you Show me what I need to do in order to have 2 (or more) different ranges in the rows?
For example, let the macro work on rows 5-6 and 15-17

Cheers

[Trav66]

Oh yeah, almost Forget....

the macro changes all old data including the current date. can you adapt it to change everything older than today but still keep the info from today?
cheers

[jomili]

Okay, to change only the older dates all we have to do is change "<=" to "<", which I've done in the below code.
On the ranges, assuming your data ranges are on the same sheet, I've done it below following my first example, for instance, you said "Data is in rows 2- 50".
So, in the original code we follow the R1C1 (RowColumn) format, so when you see "Cells(X,R)", X is the Row number, R is value from 4-17 (column number). So I simply added another section that picks up with row 52 through 100. IF you have hard and fast data sets (Like Rows 2-50 and 52-100) this is the easiest way to go. However, if you have variable data sets (for instance, today it's Rows 2-47 and Rows 58-72, tomorrow it will be different) we'll need to tackle it a different way.

Please Login or Register  to view this content.