+ Start a Discussion
Cris9931Cris9931 

is this possible to realize with a formula field?

Hi, I want to create a formula field for the following scenario... I have 4 fields:
0. Reported Start Date(date datatype)
1. Reported Start Time(time datatype)
3. Reported End Date(Date datatype)
4. Reported End Time(time datatype)

Only the following interval of time is relevant, from 22:00(10PM) - 00:00(12 AM, next day). This interval is important because I want to display the value from formula field based on this interval of time.

Example: I have from 10:00 PM - 01:00 AM,  display show 120 minutes, because we only count  minutes in this interval of time.
Example 2: I have from 08:00 PM - 10:00 PM, display nothing(blank), because is not included in this interval of time.
Example 3: I have from 11:00 PM - 00:00 AM, display 60 minutes.

And so on.... Is it possible?
Suraj Tripathi 47Suraj Tripathi 47

Hi Cristian,

Can I write trigger for this. It would become easy

Thank You

Cris9931Cris9931

Hi Suraj,

Sure. I would appreciate a lot. 

Thank you in advance
Cristian

Cris9931Cris9931

Hey Suraj, can you give me at least the logic of the trigger so that I can try to write it by myself if it's too much work for you to write it.. ?

I've managed to write the formula field but it only takes in consideration the Time fields... and it's not working correctly, only working from 10PM - 11:59 PM

 

(MIN(23*60+59.99,
MAX(22*60 ,(HOUR( SVMX_PS_Customer_End_Time__c )*60+MINUTE(SVMX_PS_Customer_End_Time__c ))))
-
MIN(23*60+59.99,
MAX(22*60,
IF(HOUR( SVMX_PS_Customer_Start_Time__c )60+MINUTE(SVMX_PS_Customer_Start_Time__c )>HOUR(SVMX_PS_Customer_End_Time__c )60+MINUTE(SVMX_PS_Customer_End_Time__c ),22*60,
(HOUR(SVMX_PS_Customer_Start_Time__c )*60+MINUTE(SVMX_PS_Customer_Start_Time__c ))))))

Can you help me a bit here?