Calculate difference between DateTime field and String 1

[jkupov]

I'm needing to determine the difference between the time which we promised our service technician would arrive and the actual time by calculating two fields PTime (the Promised time) and ATime (Actual arrival time).

Ptime is formatted as a String (5 characters) and is presented like hh:mm. So if we promised to arrive at noon it would be a string but would see 12:00

However, my field for the actual arrival time ATime is formatted as DateTime (3/8/2012 12:00:00PM)

I would like the result to be a DateTime format in case I need to drop the data into an excel pivot table and run additional calculations.

I tried using CTime ({PTime}), which I think worked for the calculation, but it returned a number.

I would like the result of the calculation to show +/- depending on whether the actual arrival time was early or late. So if the service tech promised our customer that he would arrive at noon, and showed up at 12:30, I want to see - 30:00 or (30:00) and if he showed up at 11:50 I want to be able to see +10:00 or 10:00.

Any help would be appreciated. Thank you in advance

 

[briangriffin]

First append the date of the actual arrival to the promised time and convert that to a datetime:

// {@Promised Time}
datetime(date({Actual Time}),time({Promised Time}))


Then use datediff to calculate the difference in minutes:

// {@Difference}
datediff("n",{Actual Time},{@Promised Time})

You can use standard field formatting to make the negatives display with () or -. However, adding the + to the positive values requires enabling the currency symbol (Format Field -> Number --> Customize --> Currency Symbol).

Enable the currency symbol, select Floating, make the position preceding the value, and make the currency symbol +. Then in the X2 formula for enable currency symbol enter this:

if {@Difference} > 0 then crFloatingCurrencySymbol
else
crNoCurrencySymbol

 

[jkupov]

OK- this is good. Thank you. I think I have it from here.

 

[jkupov]

Just out of curiosity, if I wanted to convert a time stored as DateTime to a number of minutes, what is the best way to do that? Example- thirty minutes stored as 00:30 and I want it to display as 30.

 

[briangriffin]

You can do that in the Format Field --> Customize dialog. Change the Hour and Seconds to "None" from the dropdown.

 

[jkupov]

Right... good. Thank you.