1

I have an object that has two Time fields: Start and End.

I want to add another, Formula field that would show me the duration also as Time.

Unfortunately, when i do simple things like Duration=End-Start the editor complains that the result is Number, not Time.

Reading on SF docs: https://help.salesforce.com/articleView?id=custom_field_time_overview.htm&type=5 the difference would be milliseconds.

how do i convert milliseconds to Time?

I tried TIMEVALUE("00:00:00") + ((0.001 *(End__c - Start__c) / 86400 )) but while that passed the validation, i got no value displayed in the Record Page

2

The difference between two time fields is a number so you would have to create a number type formula field. The reason is because you're showing the difference between the time which is always a number (number of hours, minutes, etc). You wouldn't be able to represent that as a time which is restricted to AM/PM specific times.

It shows you how to do it in your linked help doc (for hours):

enter image description here

For other units of time, just think of the conversion

  • To get seconds: divide by 1000
  • To get minutes: 1000ms/sec * 60sec/min = divide by 60,000
  • To get hours: 1000ms/sec * 60sec/min * 60min/hr = divide by 3,600,000

To get it in a time format (hh:mm), you could make your formula a text field and do some sort of the following:

TEXT(
FLOOR(
((startDate - endDate) / 60000) / 60)
)
& ':' &
TEXT(
MOD(
((startDate - endDate) / 60000), 60)
)

You could replace ':' with markers to denote Hr and Min and you could certainly move the ((startDate - endDate) / 60000) to its own formula field to make it less harsh on the eyes. The limitation of this is whether the field will be used for sums, average, etc because this wouldn't work. I'm not aware of a way to display the difference in the format you're asking and have it be useful for sums or averages in a report (if that's a requirement). I'd typically break it into discrete units that everyone can understand to sum/average (sec, min, hours).

2
  • How do i express this in base-60 for minutes? e.g. 1:30 not, 1.5 for one and a half hours – zaitsman Oct 4 '19 at 11:43
  • 1
    I added a text formula field to make it look how you'd like, but it would make the field unusable in any calculations or math. – Kris Goncalves Oct 4 '19 at 12:00

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.