I have been wrestling with getting accurate data using the customer datetime fields. I am setting them appropriately in script now (that was a headache) and all looks hunky dory in the UI. I am just trying to subtract one field (incident date) from another (custom datetime). This the response I just got on my case. This case, really. I have opened several over time with these fields.
Hello Corey,
These findings was tested on 'Case' custom field by our engineers, and then again for component area check also on 'Item' custom field, in both cases results were the same.
1. Custom field of type date/time {custevent_cxs_init_res_time} and native field {date} are timezone-sensitive, and if added as plain column to saved search results, it will change according to user's timezone setting, which is OK.
2. If the custom field is used in Result column, the result is NOT converted to user's timezone.
3. However, the {date} field is converted in all cases, as plain column and as formula column. So for user's timezone different from timezone in which the case was created there will be wrong result.
Notes:
- Even the simple formula: Formula(Date/time), formula = {custevent_cxs_init_res_time} shows that date is not converted if used in formula.
- to reproduce:
1. add formula to search results as Formula(Date/time), formula = {custevent_cxs_init_res_time}
2. add custom field 'Initial response Time (Custom)' to search results (without formula, only plain column)
3. save search
4. change timezones and refresh search, observe how formula and plain column differ.
Alternate Solution:
Unfortunately, no workaround on formula was found, we were not able to get right result with any date/time function currently supported by NetSuite search formulas.
How is this not a bigger deal? Are most companies not in multiple time zones? I cannot fathom not making this a higher priority. There is a defect attached: 241183 but it is only an S2, so I don't expect much traction. So, the calculation of the primary metric we use for our support department, their initial response, cannot be accurately measured if the support rep is in a time zone other than the one running the report. We support our customers in 4 different countries and several locations around the US. We have thousands of cases every month and pride ourselves on short response times for critical cases. We report out response times to our customers and they have come to expect low numbers. This is really unacceptable. Interestingly, if someone in our time zone (west coast) opens and then saves the case the formula will return the right time. And I mean just edit and save, no other field changes. This is not an option as we are dealing with cases in the hundreds or even thousands.Has anyone else had issues like this? I don't know what else to do other than try to eloquently explain to support that we need an escalation on this.