0

This is two custom  fields of type text 1)Takeoff, 2)Duration,i validated the end client can only enter the time in HH:MM format, and then i want the result to be displayed in a formulae field called Landing by the adding the above two fields This is Landing field formulae can someone help me out to write the formulae to find the result and display the result in text format

I need results of two fields of type text in time format to be the same time format and then the result has to be time of HH:MM format aswell text in formulae field

  • In the above Question There are two custom fields of type "Text" which are 1)Takeoff(Text) and validation is (HH:MM) format 2)Duration (Text) and validation is (HH:MM) and then the above on adding should give the result in Landing Field in HH:MM format , Please help in formule – sathish emmanuel Mar 21 '17 at 7:00
  • Are you looking for a formula field to find the difference between two time fields? which is in text? – Samuel Robert Mar 21 '17 at 9:03
  • @SamuelRobert formula but not the difference i want takeoff+duration (HH:MM) format and then save the result in the Landing Text field where format should be same (HH:MM). – sathish emmanuel Mar 21 '17 at 9:17
  • Out of curiosity, what do you intend to do when HH:MM goes beyond the 24 hour mark? Say your flight departs at 23:00 and takes 2 hours? Should it say 26:00? Or the more reasonable 01:00? – Kasper Mar 21 '17 at 13:24
  • I would like the to display at what hour the flight has arrived on the summation of two fields in Landing field and the time standard should be 24hrs @kasper – sathish emmanuel Mar 23 '17 at 5:10
0

TL;DR

Formula fields are not the right tool for the job here. I'd highly suggest using Apex, and making your Takeoff__c and Landing__c fields DateTime instead of Text.

Background

You're working with dates and times. This is not something that formula fields can handle very well. At the very least, formula fields can't handle it anywhere near as well as Apex can.

I know that the general Salesforce mantra is 'clicks before code', but if you try to do this with a formula field (if you'd even be able to stay under the character and compiled size limits) I have a feeling it would end up being a mess and a nightmare to maintain.

Salesforce doesn't have a field type strictly for holding time data (closest one is DateTime), so I'm guessing that your Takeoff__c and Duration__c fields are text fields. We can work with this, but I think it makes more sense for Takeoff__c and Landing__c fields to be of type DateTime instead. The reason using DateTime makes more sense here was mentioned in the comments on your question. What should happen if takeoff + duration puts your landing beyond 23:59? You could, of course, handle this situation manually, but DateTime does this for you, so why re-invent the wheel?

In my examples, I'll assume that you'll be changing those two fields from a text and formula field (respectively) to DateTime fields. I'll leave Duration__c as a text field in HH:MM format.

Now we can start working

We want to calculate the Landing__c date/time by adding the duration to the Takeoff__c date/time. The documentation on the DateTime class tells us that we have addHours() and addMinutes() methods, but those methods take Integer arguments. How do we turn your Duration__c text field into two integers?

The answer here has two parts. First, we use String.split() to break your duration field into two separate strings (split() actually returns a List<String>). Then, we need to take those strings, and convert them into integers using Integer.valueOf().

The code snippet for that would look like this

// In this case, the delimiter that separates hours from minutes is ':'
// It would be wise to include some null checks in your actual code.
List<String> durations = myRec.Duration__c.split(':');
// Assuming that Duration__c is formatted like 1:35, durations will contain ['1', '35']
//   at this point in the code

// Lists are zero-indexed (i.e. the first element is index 0)
// Given the format above, hours will always be index 0, and minutes always index 1
Integer durationHours = Integer.valueOf(durations[0]);
Integer durationMinutes = Integer.valueOf(durations[1]);

From there, calculating Landing__c is trivial

// addHours() and addMinutes() both return a DateTime, so method chaining like this 
//   should be possible
myRec.Landing__c = myRec.Takeoff__c.addHours(durationHours).addMinutes(durationMinutes);

// If the above gives you an error, then you'll need to use the following instead.
// Note that the second operation is applied to Landing__c instead of Takeoff__c.
// This is because DateTime operations don't alter the DateTime that we call the method on.
// If you were to use Takeoff__c for the second operation, it would overwrite
//   Landing__c, and you'd end up with a value of Landing__c that is only minutes
//   after Takeoff__c.
// myRec.Landing__c = myRec.Takeoff__c.addHours(durationHours);
// myRec.Landing__c = myRec.Landing__c.addMinutes(durationMinutes);

4 lines of code, after all is said and done. Not too shabby (and a whole lot shorter than a formula field's formula would be). It could even be taken down to a single line of code if you wanted (though I'd personally stop after taking it down to two lines of code).

If you're really insistent on keeping everything as text fields

It's still possible (easy, even) to do this with Apex. You'd just call String.split() on Takeoff__c as well. You would need to add additional logic to prevent getting nonsensical hour/minute values for Landing__c (which, while easy enough, is why I suggest using DateTime fields where possible. DateTime takes care of that for you).

List<String> durations = myRec.Duration__c.split(':');
List<String> takeoffs  = myRec.Takeoff__c.split(':');

Integer landingHour = Integer.valueOf(durations[0]) + Integer.valueOf(takeoffs[0]);
Integer landingMinute = Integer.valueOf(durations[1]) + Integer.valueOf(takeoffs[1]);

// If our minutes is > 59, we need to add at least 1 to the landingHour.
// Plain, old Integer division should do the trick.
// 75/60 = 1, 125/60 = 2.
// landingMinute % 60 ensures that our minute value is between 0 and 59 (inclusive)
if(landingMinute > 59){
    landingHour += landingMinute / 60;
    landingMinute = landingMinute % 60;
}

// Also need to do a check for landingHour
if(landingHour > 23){
    landingHour = landingHour % 24;
}

myRec.Landing__c = String.valueOf(landingHour) + ':' + String.valueOf(landingMinute);

10 lines of code is still not bad, but not nearly as good as if Takeoff__c and Landing__c were DateTime fields.

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.