making TODAY() function take specified dates

[ayordeky]

Hello everyone,


I have a question regarding TODAY() function in Powerpivot or maybe general in excel.
My model and most of calculated formulas depend on function TODAY() as I have historic data with column SOURCE DATE and ewhen i am updating every day the values are calculated from the data of that day because my TODAY function is connected with the column source date>

However, sometimes I want to see what were the values for yesterday or the other day or I want to work with the work book. What I am doing is just changing date from my PC. I am wondering if there is a way maybe with macro to somehow change in excel the date of today. So choose which is the date of today?

Hopefully I was able to explain my problem.


Many thanks in advance.

 

[mole999]

Today() is exactly that, the same as Now() its a system value, and some software stops running when the date is pushed too far out,
theres no real problem using another variable that is date orientated / formated and treat that as your default reference point

 

[barjoman]

Hello everyone,


I have a question regarding TODAY() function in Powerpivot or maybe general in excel.
My model and most of calculated formulas depend on function TODAY() as I have historic data with column SOURCE DATE and ewhen i am updating every day the values are calculated from the data of that day because my TODAY function is connected with the column source date>

However, sometimes I want to see what were the values for yesterday or the other day or I want to work with the work book. What I am doing is just changing date from my PC. I am wondering if there is a way maybe with macro to somehow change in excel the date of today. So choose which is the date of today?

Hopefully I was able to explain my problem.


Many thanks in advance.

You could use an offset which takes the value from somewhere that you can easily change. Make your formula TODAY()+A1 where A1 is the number of days to offset. 0 would be today, -1 would be yesterday, 1 would be tomorrow, etc. If you want to do it in Power Query or Power Pivot, you can "import" the value by creating a query from that cell. I don't know if this is the best option for Power Query/Pivot, but it will definitely work.

 

[akice]

Better off having a Calendar table that is not related to anything in the data model. Add Calendar[Date] to pivot as a "timeline" slicer which allows you to select date(s) at different granularity (day, month, quarter, year, etc). Then in your formulas in place of TODAY() put MAX( Calendar[Date ) ( or MIN(), or maybe even VALUES() depending on need )

 

[FDibbins]

As pointed out...

I am wondering if there is a way maybe with macro to somehow change in excel the date of today. So choose which is the date of today?

TODAY's date is - well - today...not yesterday or tomorrow, or 10 days ago, and that is exactly what excel's TODAY() function does - tell you what today's date is

If you don't want to use today's date (or even if you might want to use that, but would like the choice of other dates, put that date in it's own cell, and then reference that cell in your formulas