[SalesForce] How To Increment Datetime by Decimal

I have a packaged application installed in my salesforce which sends the Service Duration (Number Data Type) = 3,600.00 and another field Scheduled Date Time 12/21/2015 2:00 PM (DateTime Field Type).

I would like to calculate Service Duration + Scheduled Date Time, so I can get the end of the scheduled date time.

I get an error because they are of two different data types; number and Date/time.

How can I convert the number "data type" field to an hr/mins and then add that to the Date field .

Best Answer

When adding a Decimal to a Datetime in a formula, it represents a number of days. If you want to add seconds, you just need to divide by the appropriate value.

Scheduled_Date_Time__c + Service_Duration__c / (24 * 60 * 60)

If the duration were in minutes:

Scheduled_Date_Time__c + Service_Duration__c / (24 * 60)

Hours:

Scheduled_Date_Time__c + Service_Duration__c / 24

You also probably want to protect yourself against a null duration:

Scheduled_Date_Time__c + IF(ISNULL(Service_Duration__c), 0, Service_Duration__c / 24)

Related Solutions

[SalesForce] Datetime parsing

Here's an example from some code I used in a trigger on how to do this

StrtTmDt = sClsMnth + '/' + sClsDay + '/' + sClsYear + ' ' + strtTm;

So, in your case,

String date = ''; //would need to be formatted as 'dd/mm/yy'

String time = ''; //would need to be formatted as ' hh:mm aa` where aa is AM or PM

note the blank space between the ' and the hh. You need this separation. SF will automatically add :00 seconds for you so there's no need to add them unless you actually are recording seconds or microseconds for that matter.

If properly formatted as above, you could then do the following:

String dt = date + time;

DtTm = DateTime.parse(dt);

[SalesForce] Formula to calculate time between two dates, Salesforce example broken

You are correct - Looking at the SFDC Object reference, Case.closedDate is a DateTime field so line 2 and line 4 will break

Looking at line 2 and line 4 - these are manipulating days, not seconds so I'd rewrite line 2 and line 4 as:

Line 2

( 5 * FLOOR( ( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8) ) / 7) +

Line 4

MOD( DATEVALUE(ClosedDate) - DATE( 1900, 1, 8), 7) +

You should go to the doc and use the feedback form to point this out to SFDC

Best Answer

Related Solutions

[SalesForce] Datetime parsing

[SalesForce] Formula to calculate time between two dates, Salesforce example broken

Related Topic