3

I need to calculate the difference in minutes between two datetime fields inside a formula.

The following works if the dates are different

(date2__c-date1__c)*24*60

But if its the same date then this fails as the difference between the two values subtracted from each other is zero.

Is there an easy solution to this?

  • make sure both are datetime fields i tried same code in my DE org and it worked fine for two datetime fields. even if date is same it returns expected results. – rahul gawale Oct 19 '18 at 17:11
  • So the date dates were the same, just different hours/minutes? If you make a formula that states just date2__c-date1__c that should return 0. Which is my problem. – AdamJones Oct 19 '18 at 17:24
  • yes day is same I just kept difference of two minutes in two dates and it returned exact time difference – rahul gawale Oct 19 '18 at 17:26
  • 1
    In this case, you can change the return type of your formula to Text and formula will be TEXT((date2__c-date1__c)*24*60) – Pragati Jain Oct 19 '18 at 17:56
  • 1
    Thanks @PragatiJain feel free to answer question for some points, this is very useful! – AdamJones Oct 20 '18 at 1:54
5

Difference b/w two date time would never be 0 until you have exact date time value. So in your case, you can resolve your issue either by changing the return type of formula field to TEXT or NUMBER(With 0 Decimal Places).

  1. If you changed the return type to TEXT then the formula would be :

    TEXT((date2__c - date1__c)*24*60)
    
  2. If you changed the return type to NUMBER(With 0 Decimal Places) then the formula would be :

    (date2__c - date1__c)*24*60
    

I would prefer to go with the 2nd option.

  • How we can calculate the mins between only 8 hour shift? – LetMeCodeYou Apr 10 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.