Time segregation based on date field

[jayati_shrivast]

Hi All,

I have a date field in the format DD/MM/YYYY hh:mm:ss, I need to segregate time based on inoffice and outoffice

for IN OFFICE condition is " 8:30 am to 7:30 pm and rest of the time should come under OUTOFFICE.

I should get the table layout as follows:

date                                                    COLUMN1   

-----------------------------------------------------------------

04/12/2018 08:30:23                         INOFFICE

04/12/2018 20:08:23                        OUTOFFICE

For this under , COLUMN1 expression tab I am applying following condition

=if(time(date) >= '08:30:00' and time(date)<= '17:30:00' ,'InOffice',
if(time(date)>= '17:31:00' and time(date)<= '08:29:00' ,'InOffice'))

This condition is coming out to be OK in the expression tab, but I am not getting the values in the front end under COLUMN1.

pls suggest me if there is any other method by which I can implement this.

Thanks

[Vince_CH]

please try like following:
=if(time(date) >= '08:30:00' and time(date)<= '17:30:00' ,'InOffice', 'OutOffice')

[jayati_shrivast]

I applied this condition , it worked, but I am getting OUTOFFICE for all rows , even where INOFFICE should come, there also OUTOFFICE is coming.....

[Vince_CH]

Hello, can you add a field like time(date) to see if the time format coming out is exactly same as '08:30:00' or else?

[Vince_CH]

=if(time(date) >= '08:30:00' and time(date)<= '17:30:00' ,COLUMN1='InOffice', COLUMN1='OutOffice'),

pls try this?

[jayati_shrivast]

There is no such field as COLUMN1, I have taken a dimension in front end in the table and labelled it as COLUMN1 and applied if condition in its expression tab.
also I check the format it is hh:mm:ss

[Vince_CH · Accepted solution]

pls try this, this will extract only the time absolutely.
If(time(frac(Date),'h:mm:ss')>='08:00:00' and time(frac(Date),'h:mm:ss')<='20:00:00','Inoffice','Outoffice')

[Vince_CH]

Hello Jayati_shrivast , is the issue resolved? kindly please mark the correct the reply as answer if it is acceptable, or you can kinldy post your correct expressions here for the reference of others in the future. thank you!

[jayati_shrivast]

Thanks, this function worked :-).