expression issue

[#1]

hello guys,

can you help on below matter? This below is the expression using causing the issue.

=If(Num#(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) - HolidayCount < 0,OPEN_DAY_FRACTION,

   (Num#(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) - HolidayCount))

Note1: the first record from the below table is cauing the issue bcz of holidaycount.

Note 2: Input & output (looking for

EXPECTEDOUTPUT

column information) :

  Data:

QID	CREATETS	OPEN_DAY_FRACTION	CLOSEDDATEFORMAT	CLOSED_DAY_FRACTION	HolidayCount	TASK_DURATION	EXPECTEDOUTPUT
QWS170885043460	13/04/2017 09:27:15	0.606087963	14/04/2017 09:35:37	0.3997337963	1	0.0058135706	1.0058135706
QWS170889445470	13/04/2017 21:40:55	0.096597222	14/04/2017 10:23:21	0.4328819444	1	0.0965972222	0.096597222
QWS171354406609	06/06/2017 17:14:26	0.281643519	07/06/2017 12:23:03	0.5159953704	0	0.7976444	0.7976444
QWS170881595567	12/04/2017 23:52:36	0.005138889	19/04/2017 12:23:12	0.5161111111	4	2.5212500000	2.5212500000
QWS170544317729	04/03/2017 23:59:38	0.000266204	06/03/2017 16:28:28	0.6864351852	1	0.6867013889	0.6867013889

[vinieme12]

try

=If((interval(CLOSEDDATEFORMAT- CREATETS,'d h:mm:ss') - HolidayCount) < 0,

               OPEN_DAY_FRACTION,

                 interval(CLOSEDDATEFORMAT- CREATETS,'d h:mm:ss') - HolidayCount)

[#3]

not working Pujari,thanks.

[vinieme12]

can you post sample app or excel?

[#5]

attached.

[vinieme12]

Try

=if(((CLOSEDDATEFORMAT-CREATETS)-HolidayCount)<0

,OPEN_DAY_FRACTION

,(CLOSEDDATEFORMAT-CREATETS)-HolidayCount)

[vinieme12]

Check the attached

[#8]

hello pujari,

thanks for ur time,but still getting output as 0.00581xx instedof 1.00581xx.

i'm expecting the output as 1.00581xx for QWS170885043460.

kindly look into it.

[vinieme12]

Can you derive that output in an excel and show me? as per your conditions 0.00581xx  seems to be the correct answer!!

[rohitraut]

Try this:

if(Num#(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) - (HolidayCount <0) <0,OPEN_DAY_FRACTION,(Time#(interval(CLOSEDDATEFORMAT) - Interval(CREATETS),' d h:mm:ss')) )