How to add random minutes to a time field? 1

[joneas212]

I am fairly new to CR and have used these forums with great frequency and really appreciate all the great support it has provided. However, i cannot find a satisfactory answer to my problem .... perhaps I'm just trying to overthink this. Here is the problem:

When the report was first created, only the 'Service Start Time' field and the 'Duration' fields existed on the data entry form and the report. Then approx. 3 months ago, the 'Service End Time' was added. So now the bosses want the report to go back and complete the 'Service End Time' field on the reports if they are run in the future. We cannot go back and enter the End Times in the Data Entry as they are locked once submitted ....

So essentially, if End Time is Null, then add Start Time and Duration and provide the End Time.

Any ideas would be greatly appreciated.

Chris

 

[SkipVought]

End Time = Start Time + Duration

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[joneas212]

Certainly that's what I've been working to get .... but i cannot get anything to work.

//{Monitor_Tool.service_end_time} = ({Monitor_Tool.duration_service} + {Monitor_Tool.service_start_time})

//sum({Monitor_Tool.duration_service},{Monitor_Tool.service_start_time})

//sum({Monitor_Tool.duration_service}&{Monitor_Tool.service_start_time})

//dateadd('n', {Monitor_Tool.duration_service}, Monitor_Tool.service_start_time)

I think I'm way off base and just receive 'error in formula' .....

 

[pmax9999]

The basic approach will be to test initially for data in the {Monitor_Tool.service_end_time} field. Where a value exists, return that value. Where no value exists (ie, field is NULL), then calculate the value by adding the duration to the start time.

The actual formula will depend on the format of the existing columns. As an example, assuming Start and End Time are formatted as Time and the Duration is a number representing the number of minutes, the formula would look something like this:

If      Not(IsNull({Monitor_Tool.service_end_time}))
Then    {Monitor_Tool.service_end_time}
Else    DateAdd("n", {Monitor_Tool.service_duration}, {Monitor_Tool.service_start_time})

Hope this helps you get to where you need to be.

If you need further assistance, please post back with details of the data types for the 3 fields, together with samples of each of the fields.

Cheers
Pete

 

[joneas212]

Thanks Pete ... I really appreciate your assistance. I discovered that the date field was actually in String format so:

IF ISNULL({Monitor_Tool.service_end_time})
THEN ToText(TIME(DATEADD('n', {Monitor_Tool.duration_service}, CurrentDate + CTIME({Monitor_Tool.service_start_time}))),"hh:mm tt")
ELSE ToText(CTIME({Monitor_Tool.service_end_time}), "hh:mm tt")

finally worked for my situation.

Thanks again.