[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 902
  • Last Modified:

Bad Date-Time Format

Experts:

I need help calculating AVERAGE TIME. I have a field that holds a CALL_DURATION of a telephone call in this format:

CALL_DURATION
---------------------
00:14
00:20
00:05
145:14
100:10
60:15

When I try to use this formula I get the error:   BAD DATE-TIME FORMAT STRING

//@CallDurationToSeconds
(
(hour(datetime({View_smdrdata_Phone_System.CallDuration})) * 60 * 60 ) +
(minute(datetime({View_smdrdata_Phone_System.CallDuration})) * 60)
+ (second(datetime({View_smdrdata_Phone_System.CallDuration})))
) /60


This is the portion that is CR highlights as being a problem:

datetime({View_smdrdata_Phone_System.CallDuration})

I've tried different things...but I can't seem to get it....your help is appreciated.

MikeV
0
MIKE
Asked:
MIKE
  • 2
2 Solutions
 
dsackerContract ERP Admin/ConsultantCommented:
Your values are not standard date-time values, they are totals reflected as min:ss.

In other words, datetime expects a format similar to mm/dd/yy hr:mi:ss, which is not what you have. You have only a total of minutes on the left side and the seconds on the right side, delimited by the colon.

You'll need to do some simple piecing of the field. If you want to average the call duration, lose the datetime function and convert them into seconds similar to the following:

(Left({View_smdrdata_Phone_System.CallDuration}, InStr({View_smdrdata_Phone_System.CallDuration}, ":") - 1) * 60) + Mid({View_smdrdata_Phone_System.CallDuration}, InStr({View_smdrdata_Phone_System.CallDuration}, ":") + 1)

You may have to tweak this proposed solution a bit, but hope it helps.
0
 
dsackerContract ERP Admin/ConsultantCommented:
Of course, you may need to reverse the average back into the same format as the call duration. If your average ends up being only a value reflected in seconds, that could be done in the Display XSS with the following:

(CurrentFieldValue \ 60) & ":" & (CurrentFieldValue Mode 60)
0
 
MIKESoftware Solutions ConsultantAuthor Commented:
Error...

A number or currency amount is required...

I'll keep adjusting the type and see if I can get it to work...
0
 
gopatincCommented:
Just a little cleaner solution

tonumber(split({View_smdrdata_Phone_System.CallDuration},":")[1])*60 + tonumber(split({View_smdrdata_Phone_System.CallDuration},":")[2])



0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now