+ Start a Discussion
Connie SintsoConnie Sintso 

The formula should return the number of days between the account’s Last Activity Date and today

Trailhead Module 3:  Create a formula field that determines the number of days between today and the last activity date for a case's account.
Your support team has asked for improved visibility on account activity level at the time they’re helping with customer issues. Specifically, when they’re looking at a case, they’d like to see an at-a-glance view of the number of days since the case’s related account was last active. Create the formula using these requirements.The formula should be on the Case object.
The formula should be of return type Number.
The formula should be named 'Days Since Last Update' and have a resulting API Name of 'Days_Since_Last_Update__c'.
The formula should return the number of days between the account’s Last Activity Date and today.

I created the following:

I created a new custom formula field with return type number and called Days Since Last Update.
I created this formula: Account.LastActivityDate - TODAY()

I get this error message:

Challenge not yet complete... here's what's wrong: 
The 'Days_Since_Last_Update__c' formula field did not return the correct number of days between an Account’s Last Activity Date and today

Can you please assist what I did wrong?  I'm not sure if my formula is even correct with the challenge.  Please help!

Thank you.
surasura
it should be other way aroud 

 TODAY() -  Account.LastActivityDate

becuase  Account.LastActivityDate - TODAY() will return a minus no of days in difference 
Ilya PikusIlya Pikus
I used this:  TODAY() - DATEVALUE($UserRole.LastModifiedDate). It works but i get the error 

Challenge not yet complete... here's what's wrong: 
The 'Days_Since_Last_Update__c' formula field did not return the correct number of days between an Account’s Last Activity Date and today

Connie....how'd you get it to work?
 
MayankJainMayankJain
Below formula worked for me:
(Today()- Account.LastActivityDate)
Ilya PikusIlya Pikus
I get this....Error: Field Account does not exist. Check spelling.

any ideas?
sai gurijalasai gurijala
HI All,

I got the below error please help me..Thanks

Challenge not yet complete... here's what's wrong: 
The 'Days_Since_Last_Update__c' formula field did not return the correct number of days between an Account’s Last Activity Date and today
 
Amanda Malloy 5Amanda Malloy 5
I am unable to get this formula to work also.  I have tried everything listed above plus a few other ideas and still no luck.  Was anyone able to get tihs to work?
Mahesh AdiMahesh Adi

You need to use  the DATEVALUE function to convert LastActivityDate to a date field. Use custom field and subtract DATEVALUE(LastActivityDate).
Greg Burke4Greg Burke4
What other values did you change when creating the field that the task did not specify? I had a problem where I couldn't get it to run and it was because I had gone off request and changed something that the challenge didn't specify....
Satyanarayana PusuluriSatyanarayana Pusuluri
Hi,

Below Code working fine for this Challenge

TODAY() - Account.LastActivityDate as a fomula field Days_Since_Last_Update__c on Case Object.

Thanks & Regards,
Satya P
 
Shravan NShravan N
Use Following code, it worked fine for me

if(isNull( Account.LastActivityDate ),0,today()-Account.LastActivityDate)
V S PV S P
First Create a custom field called "LastActivityDate" as Date/Time on Account Object

then (TODAY() - Account.LastActivityDate)
Jonathan LautahaJonathan Lautaha
The formula:

(TODAY() - Account.LastActivityDate)

is not working and is returning:

Error: Field Account does not exist. Check spelling.

SOMEONE PLEASE HELP!
 
Marie Provost marketerMarie Provost marketer
This was driving me crazy too!

Here's what worked for me:

I created a custom field on the Account object and labeled it Last Activity Date - to calculate the value, I set this up as a formula field as a Date type and typed in LastActivityDate in the advanced formula field (this is apparently some type of hidden system field and is not visible as a standard field - go figure), checked Syntax and hit Save. The field is now named Last_Activity_Date__c.

Then I set up the Days Since Last Update custom field on the case object, again using a Formula field and the Advanced Formula editor, return type of Number. Here's the syntax that worked:
TODAY() - Account.Last_Activity_Date__c

You should be able to insert the custom field with the Insert field button (Case>Account>Last Activity Date).

Initially, I tried using the Last Modified Date but that will return an error in the Challenge check (Last Modified Date and Last Activity Date are two separate and distinct fields).
 
Michel LopesMichel Lopes
Hello,

If you have an older developer organization, like me. Try to create a new one. In my case the problem was solved.

I think this is a version bug.

My formula looks like this TODAY() - Account.Last_Activity_Date__c

Thanks & Regards,
Michel Lopes.




 
Sangram Kesari RaySangram Kesari Ray
TODAY() -  Account.LastActivityDate

This works for me.
Harsh DodiyaHarsh Dodiya
I cleared the challenge but not able to get result, I used Today() - Account.LastActivityDate but its giving me blank value. Ath the time of Field label and field name I selected numbers. Is it correct ??
Vaishali JayaprakashVaishali Jayaprakash
Hello all,

I solved the challenge the key is insert the Last Activity field in the account which exists already. 

So, TODAY() - Account.LastActivityDate​

Case - Account - Last Activity
Also, on the account record make sure you have a related task activity, here I just logged a call activity
Task Activity History
Also in your existing account log a activity and you can verify to return the number of days since last activity

Result


 
The AdmiralThe Admiral
The working formula is indeed. Since It needs to calculate days from today to the last activity.

(Today()- Account.LastActivityDate)
 
Kimberly Vanderboegh 6Kimberly Vanderboegh 6
Something must be wrong with my connecton my field is working correctly returning the number of days accurately everything is spelled correctly and still I am getting the error the 'Days Since Last Update does not exist. Weird!
Kimberly Vanderboegh 6Kimberly Vanderboegh 6
I figured it out it was all in my naming convention finally completed the challenge!:  I had the Field Name listed with the Days_Since_Last_Update_c
and only the API name was supposed to have it named as such.
Corrected Version:
Field Label Days Since Last Update 
Field Name Days_Since_Last_Update    
API Name Days_Since_Last_Update__c    
Description formula field that determines the number of days between today and the last activity date for a case's account.
Help Text
Created By Kimberly Vanderboegh, 1/2/2016 6:22 PM Modified By Kimberly Vanderboegh, 1/2/2016 7:34 PM

Formula Options

Data Type Formula [This formula references multiple objects]    
Decimal Places 0    
TODAY()- Account.LastActivityDate
saran kumar 9saran kumar 9
can anyone help me with next task "Troubleshooting Formula Errors"

User-added image
kartheek adepukartheek adepu
Challenge Not yet complete... here's what's wrong: 
There was an unexpected error in your org which is preventing this assessment check from completing: System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [test_activity_field__Latitude__s, test_activity_field__Longitude__s]: [test_activity_field__Latitude__s, test_activity_field__Longitude__s]

This is my problem please help me
INDERJEET S SINGERKHANIINDERJEET S SINGERKHANI
Thanks Satyanarayana Pusuluri
Sean GrechSean Grech
This works  (TODAY() - Account.LastActivityDate)
Biks07Biks07
Thanks Sean, This one works for me. :)
shoeb mohammed 18shoeb mohammed 18
its actually very simple, (TODAY() - Account.LastActivityDate)
Nick HepplestonNick Heppleston
This one had me stumped for far too long. In the end it turned out that I didn't have an activity logged against the Account, that was all that was missing.

Phew!
AYANAYAN
This will work (Today()- Account.LastActivityDate )
Adeline MooreAdeline Moore
I did just what Marie described above and im getting this error messsage... Please help
User-added image 
I went in and made sure The field "last name" is no required on either the Accounts or the Cases layout..
Virgilio Garcia 8Virgilio Garcia 8
Hi Adeline,

I have been doing the same challenge in trail head today.

It is quite likely that this error has to do with an active validation rule you have in your org. In that case the REQUIRED_FIELD_MISSING might be the error message of that rule. 

My advice would be that you deactivate any validation you have, so trail head  should go straight away to test your case formula

Apart from that, the challenge is expecting a formula with TODAY() and a field named LastActivityDate that you can choose from 'Insert Field' button, so the formula looks like:TODAY() - Account.LastActivityDate, so notice you do not need to create a custom field.

I hope it can help.

Best regards
 
hai.huanghai.huang
To saran kumar 9
You miss ")" at the end.
hai.huanghai.huang
I create Custom Field named as picture.
named 'Days Since Last Update'
I create Custom Field named as picture

Advanced Formula : TODAY() - Account.LastActivityDate
you can use Insert Field to insert "Account.LastActivityDate"
User-added image
Kumar__MayankKumar__Mayank
Actually the main problem is that after clicking on 'New button' in Custom Fields & Relationships ,Instead of selecting 'Formula'  many people select 'Number' as type and after that in next screen they try to insert formula which is not a correct way.
So, the correct navigation to create any formula is: 
1. From Setup, click Customize | Accounts | Fields.
2. Scroll to the Case Custom Fields & Relationships section and click New.
3. Select Formula and click Next.
Kumar__MayankKumar__Mayank
need to select 'Formula' As field type
Kumar__MayankKumar__Mayank
Then using Advanced Formula write
TODAY() - Account.LastActivityDate
It will definitely work...... :)
Imran khan TerdalImran khan Terdal
Challenge Not yet complete... here's what's wrong: 
The 'Days_Since_Last_Update__c' does not exist
udaya bhanuudaya bhanu
Challenge Not yet complete... here's what's wrong: 
The 'Days_Since_Last_Update__c' formula field did not return the correct number of days between an Account’s Last Activity Date and today

please help
udaya bhanuudaya bhanu
Field LabelDays Since Last UpdateObject NameCase
Field NameDays_Since_Last_Update  
API NameDays_Since_Last_Update__c

formula- (Account.LastActivityDate  -  TODAY())
Jason FungJason Fung
At first, I thought I have to create a custom "Case" and "Account" object and then create a formula field in the custom "Case" object. Turns out I don't need to create a custom "Case" object because there is already a built-in "Case" page (Setup > Build > Customize > Cases > Fields) that you can use. From the "Case" page, you can create a "Case Custom Fields & Relationships" and then create your "Days Since Last Update" formula. But before you create the formula, it's very important that your custom "Account" object has been created. If not, you will get a syntax error because your formula "TODAY() - Account.LastActivityDate" won't be able to find your custom "Account" object.  
Marco Pollastri 1Marco Pollastri 1
Hi all I hope this can be helpful. 
So the steps are the follows: 
1.Go to setup 
2.Search for case object "Case" 
3.Create New "Case Custom Fields & Relationships" 
4.Select Formula field
5.Put in the field Label "Days Since Last Update" and select number Type: 
User-added image
 
6.Than press next and on the right down window called function select Date&Time and then select Today: 
 User-added image
7.Now you have to select the operation, go to Insert Operation and in this case select the operation Subtraction. 
 User-added image
8.Select the "Insert field" button and select the following: 
 User-added image
9.The formula should be like this, check the Syntax and save! 

User-added image
 
I hope this was helpful. 
Thanks  
Marco 
Hans Kalse 20Hans Kalse 20
Thanks, this worked
Vamshi Krishna MettaVamshi Krishna Metta
1. From Setup, click Customize -> Case -> Fields.
2. Scroll down to the Case Custom Fields & Relationships section and click New.
3. Select Formula -> Enter  Days Since Last Update and select data type as Date -> In formul editor enter TODAY() - Account.LastActivityDate -> save.
4. Check Challenge now.
Ryan FrazeeRyan Frazee
Ok, I have read all of the above comments, and I still don´t see how to solve it.  

It seems like the problem is in the API Name doesn´t allow for the "double underscore."  When I put in 1 underscore, it doesn´t return the error, but the API in Trailhead says it isn´t there.

The first image is the result of the exam, when I use 1 underscore "Days_Since_Last_Update_c"
User-added image

The second is the result when I use 2 underscores in the API name.  It won´t let me save it, because the custom field can´t have "two consecutive underscore characters."  So, now I am stuck.  Help.

This is a screenshot to show the "error" that is returned is the fact that it doesn´t accept a double underscore.
SarathChandra BoggarapuSarathChandra Boggarapu
Hi,,,Give the field name as "Days_Since_Last_Update", It automatically takes the API name as the __c one.
Ryan FrazeeRyan Frazee
That did it! Thank you!
Caleb KuesterCaleb Kuester
I was trying to use LastModifedDate. That didn't work. This did work: 
TODAY() - Account.LastActivityDate
gerah mgerah m
If you are still having issues, here's what to do. Go to setup > On Quick Find search box type Objects.Click Objects that appears on the left menu. A list of all custom objects will appear. Now, make sure you have an object named Case. If not make sure you create one by clicking the tab New Custom Object. 
Now on the quick find  type Customize  > click Customize . On the dropdown list you will see your object named Case. Click the drop down menu on the Cases . Click the Fields . Scroll down to Case Custom Fields & Relationships . Click New > select Formula > on the label box put  Days Since Last Update dont fill out the field name. it will populate automatically>select Number > In the text area type TODAY() - Account.LastActivityDate . Click Check syntax . If its green you are green to go. Click Next, Next save . This should work.

 
Gouranga DasGouranga Das
Today()- Account.LastActivityDate --Works Fine.
Kevin Walsh 33Kevin Walsh 33
Gouranga Das is correct: TODAY()-Account.LastActivityDate 

Screen Grab
Maikel UwlandMaikel Uwland
''TODAY() - Account.LastActivityDate'' Works fine to get your points, but to get actually the correct data you can use this code:
TODAY() -  DATEVALUE(LastModifiedDate). The command DATEVALUE converts 'the LastModifiedDate' into a number!
Maggie Wang 6Maggie Wang 6
Trailhead give this result
User-added image
but i got this formula work well...
User-added image
 
arijit Gupta 10arijit Gupta 10
I am facing a similar issue, though the count last activity days work. But we want to add another field to count the days from Case Open to System Date. So I added a a formula in the field and the syntax is correct. But the count is wrong, atleast for every case its difference of 20 days .. 
My Formula - Syntax is OK . . 
Created a Custom Formula Field within Case and added the formula.

(5*( FLOOR((TODAY()- DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD(TODAY() - DATE( 1900, 1, 8), 7)))-(5*( FLOOR((Date_Opened__c - DATE( 1900, 1, 8) ) / 7 ) ) + MIN( 5, MOD( Date_Opened__c - DATE( 1900, 1, 8), 7)))
Richard Jones 40Richard Jones 40
Lots of complication here :p I just used TODAY()-Account.LastActivityDate, and it worked first time :)
P3t3r_L@ntzP3t3r_L@ntz
OMG!!! I was doing it backwards....
Jeffrey PoczatekJeffrey Poczatek
Are you getting this error ('Days_Since_Last_Update_C'?
User-added image
If so, select the blue dropdown arrow:

User-added image

And 'Create a Trailhead Playground'

User-added image

Redue the challenge in the new playground. You probably just needed a fresh playground. Hope this helps!
Rajan Vegesna 6Rajan Vegesna 6
This will work

Today()- Account.LastActivityDate
Shiva Mali 13Shiva Mali 13
Today() - DATEVALUE(LastModifiedDate) or Today()- Account.LastActivityDate working fine
Siddhartha Mitra 15Siddhartha Mitra 15

Hi All,

Despite all the permutation and combinations, the trailhead challenge is still not getting cleared for this one. Looks like a bug over here. Could you please confirm/suggest an alternative in this scenario?

I had finally zeroed in on the formula:

ABS(Account.LastActivityDate) - TODAY())

This would ensure that the negative values are also taken care of, depending on the due dates for the account's closed activities accordingly.

The functionality is working as expected in my synced developer org (with Trailhead) and the 'Days Since Last Update' field is showing the correct values as well.
However, the trailhead challenge is not allowing me to progress and keeps on showing the same message as pasted below in the snapshot. This is when the reports are also showing the correct values.
User-added image

This is my account report in consideration with the last activity date highlighted in the first instance:

User-added image

And consequently, the case detail page (for a child record of the concerned test account record) also has the correct value populated in the Days Since Last Update field. Today is September 22nd and the value correctly comes up as 5.
Snapshot below:

User-added image


Regards,
Sidd

Siddhartha Mitra 15Siddhartha Mitra 15
Typo in the above comment:

Formula :   ABS(Account.LastActivityDate - TODAY())
Charu HansCharu Hans
Hello,
I am able to clear the challenge. But somehow I am not able to see the result under the field "Date Since Last update in any case".
Kindly help.User-added image
Regards
Charu 
 
Siddhartha Mitra 15Siddhartha Mitra 15
Hi Charu,

Did you check if at least one of the activities under the parent account has its status 'Completed' with a set due date?

Also, could you please share the exact formula employed by you in this scenario? The functionality is totally working fine for me but i am still unable to proceed through in trailhead.


Regards,
Siddhartha

 
TAYLOR WIMBERLY 14TAYLOR WIMBERLY 14
I keep getting an error when trying to crease a new custom formula field on the Case object. The error message is, "Error: The custom field name you provided Days_Since_Last_Update__c on object Case can only contain alphanumeric characters, must begin with a letter, cannot end with an underscore or contain two consecutive underscore characters, and must be unique across all Case fields"

I am using the Field Label, "Days Since Last Update" and it auto-populates the Field Name as "Days_Since_Last_Update" instead of the "Days_Since_Last_Update__c" that is in the Hands-on Challenge notes. See attachment.

challenge-error
Mil S.Mil S.

I am able to create the formula without any syntax errors (ABS(TODAY()-DATEVALUE(LastModifiedDate)). 

However, i am still not able to complete the challenge.  I am getting the following error. 

User-added image

Please help.  

Thanks,
Mil 

 

Karthik TathiReddyKarthik TathiReddy
The below is correct formula to print Days between Accounts last activity date and present.
Today()-Account.LastActivityDate

The above expression will always prints zero unless you have any records under Activiy History related list under that account.
User-added image

Please mark as solution if you understand this and resolved your issue..
 
Kartikeya GaurKartikeya Gaur
Today()- Account.LastActivityDate Its working fine.
muri bellomuri bello
Today()-Account.LastActivityDate work very fine. Thanks Karthik TathiReddy
Umesh BetiUmesh Beti
ABS(Account.LastActivityDate  -  TODAY()) ,
it's work for me 
Connie Sintso .
geoffrey bauer 3geoffrey bauer 3
Today() - DATEVALUE(LastModifiedDate) is what  I am using.

The message is   Challenge Not yet complete... here's what's wrong: 
There was an unexpected error in your org which is preventing this assessment check from completing: System.DmlException: Insert failed. First exception on row 0; first error: FIELD_CUSTOM_VALIDATION_EXCEPTION, POPULATE ACCOUNT NUMBER PLEASE: [Name]

Any ideas?
Aney NaeemAney Naeem

Hello Everyone --

My name is Aney and i am new on salesforce.i have been stuck in this tralihead challage "Create a formula field that determines the number of days between the end date of a contract and today." i would try all the above formulas to complet this challange but these formulas not work this particuler challage of "Module Formulas & Validations" then i create a new formula according to this challage *  EndDate  -  TODAY() * its work (Y)

 

Thanks


 

Daniel Sánchez OrúsDaniel Sánchez Orús
Hi Everyone,

Today (2018), the formula for the TrailHead: Formulas & Validations -> Use Formula Fields is:

EndDate - TODAY()

Formula return type Number, decimal places 0.
Pedro RodriguesPedro Rodrigues
Days Remaining (Number) =
EndDate - TODAY()

Contract > Contract End Date
Raymond BeechamRaymond Beecham
EndDate - TODAY()

Worked for me (2020)
DIAKHATE NOUMOUDIAKHATE NOUMOU
I solved the problem 
Today() - DATEVALUE(LastModifiedDate)