Turn around hours

[former_member290153]

Hi All,

Am having fields  with date type Number and date and I want to calculate hours between these fields two fields.

any clue please help.

Regards,

Priyanka.

[raghavendra_hullur · Accepted solution]

Hi,

What is the format of date number you have?

And what is the format of date for date field?

Thanks,

Raghavendra

[former_member290153 · Accepted solution]

Hi,

Please find the below data type and format.

DATE_RECEIVED -Data type is Date and format is(DD-MM-YY)

VERBAL_NOTICE :Number (4,0)

i want to calculate: DATE_RECEIVED - VERBAL_NOTICE = Turn around hours

Thanks!

Priyanka.

[raghavendra_hullur · Accepted solution]

Hi,

Can you provide sample data for VERBAL_NOTICE column value? From your reply it seems to be of 4 digits only, so what is the actual data it contains?

Thanks,

Raghavendra

[former_member290153 · Accepted solution]

Hi,

As of now i don't have any data  in column Null is there.

Regards,

Priyanka.

[raghavendra_hullur · Accepted solution]

Hi,

VERBAL_NOTICE will it be having date value in the form of number?

It will be difficult to decide what exactly it will have unless you have some data.

To calculate the hour difference, you need to have 2 date values.

So, if your VERBAL_NOTICE is having a valid date having any combination of day, month and year , it can be converted to a date data type by splitting the day, month and year values and then using them into a cDate function to convert to Date.

cDate(year, month, day) gives a date. Then that can be used to find the difference of hours by something like as below:

(DATE_RECEIVED - <VERBAL_NOTICE converted as a date>) * 24.

So, unless you get an idea of VERBAL_NOTICE, it's difficult to proceed with further conversions and calculations.

Correct me if I am wrong with VERBAL_NOTICE number value.

Thanks,

Raghavendra

[former_member290153 · Accepted solution]

Hi Raghavendra,

Thanks for Reply and Explanation.

You are right,until we will get data it will be difficult to find it.

i have sent same question to client.once i ll get data i will try to convert as you explained.

Once again thanks for Reply.

Thanks,

Priyanka.

[former_member290153 · Accepted solution]

Hi Raghavendra,

Verbal_notice having the number value i.e (100)

and DATE_RECEIVED is date value i.e(24-12-14)

Please let me know how to calculate Turn around hours?

Regards,

Priyanak.

[abhilash_kumar · Accepted solution]

Hi Priyanka,

What is 100 - hours?

What according to you should be the Turn around hours for the above expression?

-Abhilash

[former_member290153 · Accepted solution]

Hi Abhilash,

Sorry for late reply Becoz of not proper data i couldn't reply ..My Actua requirement is To calculate Hours between two dates and time below are my filed.

DATE_AUTH_RECEIVED,data type, (Date) -Format=(MM-DD-YY)-(24-12-14)

TIME_AUTH_RECEIVED,data type ,Numeric(4,0) -Format=(600)

DATE_VERBAL_NOTICE,Data type, (Date)-format=(MM-DD-YY)-(03-01-15)

TIME_VERBAL_NOTICE ,data type Numeric(4,0)-Format=(600)

From the above 4 filed i should calculate turnaround hours.

Please let me know how i can find  it.

Thanks in advance.

Regards,

Priyanka.

.

[raghavendra_hullur · Accepted solution]

Hi,

The time parts you have provided(600), are they in minutes or seconds?

Also, the format for date you have mentioned and actual data included for dates is not matching for DATE_AUTH_RECEIVED.

Thanks,

Raghavendra

[former_member290153 · Accepted solution]

Hi Raghavendra,

VERBAL_NOTICE is a four digit number representing just hours and minutes.  So 100 would be 1:00pm and 600 would be 6:00pm. 

Both dates format is (DD-MM-YY)

Regards,

Priyanka

[raghavendra_hullur · Accepted solution]

Hi,

You hour part is still not clear, so is the am pm part where you mentioned 600 means 6:00 pm. You say time parts are in number, then how do you decide on am pm part? is there another DB field for that?

But assuming first 2 digits as hours and last 2 digits as minutes, please try below steps and check:

Create a formula (say hours) with below content.

stringVar verbal_time := cStr(<your database field TIME_VERBAL_NOTICE>, '#');

stringVar auth_received_time := cStr(<your database field TIME_AUTH_RECEIVED>, '#');

stringVar hour1;

stringVar minutes1;

stringVar hour2;

stringVar minutes2;

dateTimeVar verbal;

dateTimeVar auth_received;

//Steps to get hour and minutes part and then convert to hours

hour1:= if len(verbal_time) = 3 then left(verbal_time, 1) else left(verbal_time, 2);

minutes1 := 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) = 3 then left(auth_received_time, 1) else left(auth_received_time, 2);

minutes2 := 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)

Try this and update if anything else is missed or time part needs to be used differently.

Thanks,

Raghavendra

[former_member290153 · Accepted solution]

Hi,

Thanks for Response.I will try and let you know..

Thanks!.

Priyanka.

[former_member290153 · Accepted solution]

Hi Raghavendra,

here i got the time part clarification.

The TIME fields are in military time, so 11:30PM would be 2330 in the TIME field.  And 8:15AM would be 0815, which becomes 815 since the TIME field is a number. 

Your query is perfect,stilli need to change the Hours part and min part i guess..

Please  let me know how to modify it.

Thanks!.

Priyanka.

[raghavendra_hullur · Accepted solution]

Hi,

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>, '#');

stringVar hour1;

stringVar minutes1;

stringVar hour2;

stringVar minutes2;

dateTimeVar verbal;

dateTimeVar 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).

Thanks,

Raghavendra

[former_member290153 · Accepted solution]

Thanks alot...

[former_member290153 · Accepted solution]

Hi,

In hours part am getting A number is required error in below step. i have highlighted  that

//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);

[raghavendra_hullur · Accepted solution]

Hi,

I hope you have figured out by this time what change is required. Apologies, missed a string conversion of zero

You need a change in below part:

if len(verbal_time) in [1, 2] then "0"

Same for hour2 part too.

Thanks,

Raghavendra

[former_member290153 · Accepted solution]

Yaa i have done..No need of Apologies.Once again thanks a lot for your time and help.

Regards,

Priyanka.