Duration
Creating a formula field to show the Duration of an Appointment or Project Activity based on the Start Date and Time, and the End Date and Time.
The output of this formula will be formatted as Days, Hours and Minutes. For example, this field would read 0 Days, 3 Hours 30 Minutes.
If the Start Date Time is: 9/25/24 at 10AM and the End Date Time is: 9/25/24 at 1:30PM, the output of this field would read 0 Days, 3 Hours 30 Minutes.
TEXT(
FLOOR( i360__End__c - i360__Start__c )
) & " Days " &
TEXT(
FLOOR(MOD((i360__End__c- i360__Start__c )*24,24))
) &" Hours " &
TEXT(
ROUND(MOD((i360__End__c- i360__Start__c )*1440,60),0)
) &" Minutes "
Arrival Window
Creating a formula field to show an Arrival Window timeframe based on the Start Time on the Appointment or Project Activity.
The following formula gives you the Computed Start Date/Time plus 1 Hour.
If the Computed Start Date/Time is 9/25/24 at 10AM, the output of the Formula below would be 9/25/24 at 11AM.
IF ( DATEVALUE(i360__Computed_Start_DateTime__c) >=
DATE ( YEAR (DATEVALUE(i360__Computed_Start_DateTime__c)),3,1)
+
(
14-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Computed_Start_DateTime__c)),3,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Computed_Start_DateTime__c)),3,1) - DATE (1900,1,7),7))
)
&&
DATEVALUE(i360__Computed_Start_DateTime__c) <
DATE ( YEAR (DATEVALUE(i360__Computed_Start_DateTime__c)),11,1)
+
(
7-
CASE( MOD(DATE ( YEAR (DATEVALUE(i360__Computed_Start_DateTime__c)),11,1) - DATE (1900,1,7),7) ,
0,7,MOD(DATE ( YEAR (DATEVALUE(i360__Computed_Start_DateTime__c)),11,1) - DATE (1900,1,7),7))
),
IF(
OR(
VALUE( MID( TEXT( i360__Computed_Start_DateTime__c -0.125), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Computed_Start_DateTime__c - 0.125), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Computed_Start_DateTime__c- 0.125), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Computed_Start_DateTime__c - 0.125), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Computed_Start_DateTime__c- 0.125), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Computed_Start_DateTime__c- 0.125), 12, 2 ) ) < 12,
"AM",
"PM"
),
IF(
OR(
VALUE( MID( TEXT( i360__Computed_Start_DateTime__c- 0.1666666666666667), 12, 2 ) ) = 0,
VALUE( MID( TEXT(i360__Computed_Start_DateTime__c - 0.1666666666666667), 12, 2 ) ) = 12
),
"12",
TEXT( VALUE( MID( TEXT(i360__Computed_Start_DateTime__c - 0.1666666666666667), 12, 2 ) )
-
IF(
VALUE( MID( TEXT(i360__Computed_Start_DateTime__c - 0.1666666666666667), 12, 2 ) ) < 12,
0,
12
)
)
)&
":" &
MID( TEXT( i360__Computed_Start_DateTime__c - 0.1666666666666667), 15, 2 )
& " " &
IF(
VALUE( MID( TEXT( i360__Computed_Start_DateTime__c - 0.1666666666666667), 12, 2 ) ) < 12,
"AM",
"PM"
))
The "arrival time start" field was working beautifully in output format, but needed the UTC adjustment. We found that we could enter TIMEVALUE(i360__Start__c) -25200000 and that would adjust for -7 hours, as it's 25,200,000 milliseconds for 7 hours.
To add our two hours for the end of the arrival window, you are then able to use the following Formula:
TIMEVALUE( Arrival_Time_Start__c) + 7200000
For Time Zone formula considerations see also: Generate Start and End Times in Correct Time Zone