Trouble displaying correct time difference 1

[mvalley]

I have 2 time fields I am trying to display the difference between.
Example:
v_CRA_20_Billing_Summary.time_pat_in_room results equal 806
v_basic_booking_data.book_start_time results equal 750

I am looking for the results to display 16, however the results displayed is 56

I have 2 formulas.

@Difference:
if({v_CRA_20_Billing_Summary.time_pat_in_room}>{v_basic_booking_data.book_start_time}) then {v_CRA_20_Billing_Summary.time_pat_in_room}-{v_basic_booking_data.book_start_time} else 0

@Total Time:
If{@difference}>59 then {@difference}-40 else{@difference}

How can I adjust my formulas to give me the results I need, or do I need a new formula?

 

[IanWaterman]

Looks like someone has decided to display time in a odd fashion.

750 = 7mins 50sec
806 = 8min 6 sec

Difference 16 s

YOu will need to break down a calculate seconds.

This approach assumes only minutes and seconds, if it can include hours you will need to convert them to secs too.

@StartTime

local numbervar sec:= tonumber(right(totext(timefield, 0,""),2));
local numbervar min:= tonumber(left(totext(timefield, 0,""),length(timefield)-2));

60*min + sec;

Do same for End time and then just create a formula which subtracts Strat time from end time

Ian

 

[mvalley]

Sorry for not explaining properly.
750 is 7:50am and 806 is 8:06am

 

[IanWaterman]

Same principle applies, you need to convert to mins rather than seconds.

Can a start and end straddle across two days? If yes then a slightly different approach is required.

Ian

 

[mvalley]

Following instructions as noted above,(based on info that 750 is 7 hr 50 mins)
local numbervar min:= tonumber(right(totext(timefield, 0,""),2));
local numbervar hr:= tonumber(left(totext(timefield, 0,""),length(timefield)-2)); When I put the timefield in after length(timefield)-2)I get an error message "A string is required here"
Suggestions??

Also, Can a start and end straddle across two days? the answer is NO.

 

[IanWaterman]

Sorry for length you will need to convert to text too

length(totext(timefield))

Ian

 

[mvalley]

That works Ian, thank you for supplying me with the correct formula. You Rock!! Mary