How can I calculate the hours between two date/tim...
# suiteanalyticsy
Yadigar
02/20/2020, 4:33 PMHow can I calculate the hours between two date/time fields? Thanks in advance.
g
Gregory Jones
02/20/2020, 4:47 PM({date1} - {date2})*24Gregory Jones
02/20/2020, 4:48 PMFormula (numeric), and i'd suggest using the rounding function as well
y
Yadigar
02/20/2020, 4:50 PMThanks for the quick response I get an error of: An unexpected error has occurred. Please click here to notify support and provide your contact information.
g
Gregory Jones
02/20/2020, 4:59 PMHmmmmm
Gregory Jones
02/20/2020, 5:00 PMWithout knowing what else you have in your search, I'm stumped as to what could be causing it. If you remove that formula, it works?
y
Yadigar
02/20/2020, 5:02 PMYep. it's a really basic search of a custom record which hold 2 fields (type date/time) and I would like to have a formula that will calculate the hours between those dates.
g
Gregory Jones
02/20/2020, 5:19 PMWhat happens if you just subtract one field from the other?
{date1} - {date2}y
Yadigar
02/20/2020, 6:36 PMStill the same error. Very strange! ☹️
g
Gregory Jones
02/20/2020, 6:52 PMThat is very bizarre. Is it possible that they are formatted to appear like date/time fields, but are actually text?
Gregory Jones
02/20/2020, 6:53 PMOther than that, i'm all out of ideas. Sorry! If you post a screen shot of how you have the search configured, that might help.
y
Yadigar
02/20/2020, 7:00 PMThat it the search. the formula for days are fine
Yadigar
02/20/2020, 7:01 PMSearch of a custom record
Yadigar
02/20/2020, 7:02 PMthese field's type are date/time i.e 20/2/2020 155600
g
Gregory Jones
02/20/2020, 7:04 PMHave you tried duplicating the formula for days and multiplying that by 24? I see the NVL in there which means that some don't have an end date, right? That could be the cause of the error.
y
Yadigar
02/20/2020, 7:08 PMNow it's not showing the error. but it show the day as hours but i would like to see 8 hours instead of 0. the purpose of that is SLA
g
Gregory Jones
02/21/2020, 2:29 PM0 hours appears when start date = today? You could probably use CASE or DECODE to check if the formula will = 0, and if it does then output 8, and if it doesn't then output the result of the formula.
y
Yadigar
02/23/2020, 8:11 AMI'm manged to achieve that with this formula (text): TRUNC(NVL({custrecord_end_date},{today}))-TRUNC({custrecord_start_date}) || ' Days ' || CEIL(ABS(TO_CHAR(NVL({custrecord_end_date},{today}),'hh24')-TO_CHAR({custrecord_start_date},'hh24'))) || ' Hours '