Turn around hours
Am having fields with date type Number and date and I want to calculate hours between these fields two fields.
any clue please help.
Raghavendra Hullur replied
The hour and minutes part holds good for even 24 hour format as explained by you.
But, we need a change for hour and minutes check from 4 digit value as 1230 AM will be 0030 and same will be displayed as 30 (minutes part) as per your latest response.
So, please check this modified formula content:
stringVar verbal_time := cStr(<your database field TIME_VERBAL_NOTICE>, '#');
stringVar auth_received_time := cStr(<your database field TIME_AUTH_RECEIVED>, '#');
//Steps to get hour and minutes part and then convert to hours
hour1:= if len(verbal_time) in [1, 2] then 0 else if len(verbal_time) = 3 then left(verbal_time, 1) else left(verbal_time, 2);
minutes1 := if len(verbal_time) in [1,2] then verbal_time else right(verbal_time, 2);
//Adding hours and minutes to date part
verbal := dateAdd('n', toNumber(hour1)*60 + toNumber(minutes1), <your database field for DATE_VERBAL_NOTICE> );
hour2:= if len(auth_received_time) in [1, 2] then 0 else if len(auth_received_time) = 3 then left(auth_received_time, 1) else left(auth_received_time, 2);
minutes2 := if len(auth_received_time) in [1, 2] then auth_received_time else right(auth_received_time, 2);
auth_received := dateAdd('n', toNumber(hour2)*60 + toNumber(minutes2), <your database field for DATE_AUTH_RECEIVED> );
//Calculating the difference in hours from 2 datetime variables verbal and auth_received
dateDiff("h", verbal, auth_received)
This holds good as long as there will be some value for time fields (greater than zero).