Converting calculation on Date back to Timestamp

Discussion created by david.smith on Oct 23, 2016
Latest reply on Nov 2, 2016 by David Moyer

After many hours of trying and researching I managed to find a solution that works out if a service call booked on a particular day will have it's SLA window fall on a weekend, and so skip to the Monday following. However, I am hoping someone in the community may be able to help me find the right syntax to add back in the time part of the timestamp field.


I have a timestamp field called "Onsite_deadline" that is labelled below as Onsite SLA. It takes a variety of variables such as time a call is logged, and contracted hours that an engineer will arrive on site (e.g. from 9am to 5.30pm), the SLA window that can be Next Business Day or a fixed period of hours, and then calculates the time and date when the job must have an engineer arrive. In linear mode it will calculate the Onsite SLA typically to fall over a weekend so it needs to have a revision on the date part. However, using the calculation below for the field successfully works out if it needs to skip the weekend and return the right date field, but I am unable to find the right code to strip out the time value from the field before the calculation for the weekend strip out is made, and then concatenate the revised date with weekend calculation built in with the time value into a timestamp field. Right now it returns 00:00 as the time field. Is anyone able to advise how to achieve this?



Let ( [

DaysToAdd =
Case (
DayOfWeek ( Onsite_deadline ) = 1; 1; // Sunday
DayOfWeek ( Onsite_deadline) = 2; 0; // Monday
DayOfWeek ( Onsite_deadline) = 3; 0; // Tuesday
DayOfWeek ( Onsite_deadline) = 4; 0; // Wednesday
DayOfWeek ( Onsite_deadline) = 5; 0; // Thursday
DayOfWeek ( Onsite_deadline) = 6; 0; // Friday
DayOfWeek ( Onsite_deadline) = 7; 2)]; // Saturday
Date ( Month ( Onsite_deadline ); Day ( Onsite_deadline) + DaysToAdd ; Year ( Onsite_deadline) & "" & Time((Onsite_deadline);(Onsite_deadline) ;(Onsite_deadline))