business hours and rest as non business hours.

[pskumars]

HI All,

I Have date data below.

i need to calculate time between 8:00 to 20:00 as business hours and rest as non business hours.

DateTime

11/16/2016 15:00

11/16/2016 15:30

11/17/2016 14:30

11/21/2016 9:30

11/21/2016 10:00

11/21/2016 10:30

11/21/2016 11:00

11/21/2016 11:30

11/22/2016 7:30

11/22/2016 12:00

11/23/2016 11:30

11/24/2016 11:30

11/29/2016 9:30

12/1/2016 10:30

12/1/2016 11:00

[sunny_talwar]

May be like this

LOAD DateTime,

     If(DateTime >= MakeDate(8) and DateTime <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag],

     ....

FROM ...

[pskumars]

Not working Sunny,thanks for suggestion

[neelamsaroha157]

Does this work for you..

[pskumars]

no

[sunny_talwar]

Forgot to add Frac function

LOAD DateTime,

    If(Frac(DateTime) >= MakeDate(8) and Frac(DateTime) <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag],

    ....

FROM ...

[sunny_talwar]

Found another issue

If(Frac(DateTime) >= MakeTime(8) and Frac(DateTime) <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]

[pskumars]

Hi Sunny ,

it works but there is an issue , 20:00 also adding as non business hours.

[sunny_talwar · Accepted solution]

May be try this

If(Frac(DateTime) > MakeTime(7, 59, 59) and Frac(DateTime) < MakeTime(20, 0, 1), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]