Scenario
There are Time of Day type custom fields for "Time Started" and "Time Finished".
User wants to automatically calculate the total time (in minutes) between these two fields and show it in another custom field "Time Spent".
For example:
- Time Started = 1:00 pm
- Time Finished = 2:30pm
The Time Spent field should show 90.
Solution
This formula can be used to calculate the total time in minutes:
((CASE WHEN (FLOOR(({fieldforendtime}-{fieldforstarttime})*24)) < 0 THEN FLOOR(({fieldforendtime}-{fieldforstarttime})*24)+24 ELSE (FLOOR(({fieldforendtime}-{fieldforstarttime})*24)) END)*60) + (CASE WHEN MOD((({fieldforendtime}-{fieldforstarttime})*1440), 60) < 0 THEN MOD((({fieldforendtime}-{fieldforstarttime})*1440), 60)+60 ELSE MOD((({fieldforendtime}-{fieldforstarttime})*1440), 60) END)
Just change the {fieldforendtime} and {fieldforstarttime} to the field IDs of the fields in your account.
For this to work on the custom field, the Store Value should be unmarked on the custom field record.
Note that if the Store Value is unmarked in an existing field that already has stored value in some records, then these data will be lost, so it is highly suggested that you either:
- Back up the data before doing any changes
- Create a new field to be used moving forward
It is also best practice to test first in Sandbox or test accounts before making any changes in Production account.
Also, note that since the Store Value is unchecked, the value of this field will not show in the Results of Saved Searches.
As a workaround, try steps similar to what is documented in SuiteAnswers 63178 - Custom Field Populated by Summary Saved Search Appears Blank When Used in Another Saved Search, or just use a Formula field in the search and use the same formula provided.
-----
If you find this information useful, let us know by reacting or commenting on this post!