[From Wrike] Using the NOW() function to reference today ๐
Hi Community,
ย
My name is Mobeen, and Iโm a Senior Technical Services Consultant on Wrikeโs Professional Services team. Today I would like to help you explore the NOW() function and some of its use cases. Hopefully, this will you manage your work better!
ย
What is the NOW() function?
The NOW function helps you reference todayโs date in a dynamic way. Another way you can think of this could be โTodayโ or โCurrentdayโ. In Wrike Analyze, you can find NOW() by moving over to the function tab and jumping to the โDate & Timeโ section:
ย
How to use NOW()?
To use NOW(), you need to add an input parameter. This parameter must be a date type field, and this controls the desired output level from todayโs date, i.e., day, month, year, or time. You can select the output level by clicking on the field in your formula and selecting the desired grouping:
ย
In my screenshot above, the formula: NOW (Days in Project Start Date) with the grouping of โDaysโ selected, will give me todayโs date.
ย
When can I use NOW()?
NOW() must be used in conjunction with a DDIFF (date difference). There are plenty of use cases for this, including:
ยทย ย ย ย ย Days left in task duration
ยทย ย ย ย ย Days task has been overdue for
ยทย ย ย ย ย Days task has been active since
ยทย ย ย ย ย Calculating SLA/OLA limits
ยทย ย ย ย ย Analyzing team performance and efficiency
ยทย ย ย ย ย Days since the last change
...and many more!
ย
Letโs look at some examples:
Example 1 โ How many days do I have left to complete my task?
Here is a look at my table which displays the difference between the due date and today, today being dynamic every day.
ย
The positive values show how many days I still have pending until the due date, and the negative values help me understand if I have been overdue already for a number of days. Letโs look at the formula:
ย
It is a simple date difference between โTask Due Dateโ and โNOWโ, NOW being the current date.
ย
Example 2 โ How many days since the request was received?
In a fast-paced environment, quick resolutions are critical. If you have SLAs to follow or simply want to see how many days it has been since a new request came in, this could be a good example for you. Here is a look at the final table:
ย
Here, again, positive values show how many days it has been since the start date and negative values show how many days further to the start date of a task. Letโs check the formula:
ย
Did you notice I had to change the order of my fields inside the DDIFF compared to the previous example? The order represents how the two dates are subtracted. Since I want to calculate the number of days it has been since the start date as positive values, I moved the NOW function as the first input to my DDIFF.
ย
Did you find this helpful? Do you have different use cases for NOW()? Let us know below how you are using it in your analytic boards. See you in the next Analyze post!
ย
ย
Mobeen Tahir Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Mobeen Tahir Wrike Team member Become a Wrike expert with Wrike Discover
Hi Mobeen,
Thank you a lot for this useful article related to Analyze and NOW function! I have one questions if I may.
I wanted to see when was the last and only last change in any of the task within the project (to see the latest activeness). Is this possible? So far I created the below formula, but the numbers of days are really high and I think they might not capture only ONE, LAST change from all task status changes:
DDIFF (NOW ([Days in Task Status Change History Previous Change Date]),[Days in Task Status Change History Previous Change Date])
Kind regards,
Mateusz
Hello Mateusz Pawlak,
Thank you for your feedback and I am glad you found the article useful!
Your use case is quite interesting and a good use of the NOW function to calculate that. First thing I'd like to mention is, it would be better to use the field "Task Status Change History Change Date" instead of the field you used ""Task Status Change History Previous Change Date". The latter gives the date of a previous change and since we want to calculate the latest change, we should use the first field I mentioned. You can find more information on these fields here: Wrike Analyze - Available Data
To calculate the time difference between the the status change and the current time, you are on the correct path. The base formula you have is correct (by replacing the field as I mentioned above) and your use of NOW is set up correctly. But to make sure we only capture the latest change we would need to define that in the formula with the help of multi-pass aggregation. Your formula would look like this:
To explain, I am using the MIN function (minimum) to group all my status change dates and capturing only the smallest one. The smallest value should represent the latest status change that happened. If you are using a pivot table with task names as one of your columns of the table, this will automatically run one by one for each task and capture the minimum value of the date difference between NOWย andย Task Status Change History Change Date.
Please let me know if this formula works for you, or if you have any more questions.
Thank you.
ย
Mobeen Tahir Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Mobeen Tahir Wrike Team member Become a Wrike expert with Wrike Discover