Use SWITCH with TIME() / time frames.

[RemiAnthonise]

Hi guys,

I use this formula to calculate some timeframes.

Timeframe=
SWITCH(TRUE();
Patient[planned dismissal] > TIME(07;00;00) && Patient[planned dismissal] < TIME(15;00;00); TIME(15;00;00);
Patient[planned dismissal] > TIME(15;00;00) && Patient[planned dismissal] < TIME(23;00;00); TIME(23;00;00);
TIME(07;00;00))

It doesn't work, my outcome is always the 'else': 07:00. See image below + expected results. How do I handle this?

[MFelix]

Hi @RemiAnthonise ,

Is the planned dismissal column formatted as time or date? 

In the test I have made it work correctly with your calculation.

Regards,

MFelix

[RemiAnthonise]

Hi @MFelix ,

Thanks for your reply. I see that in my formula

Timeframe=
SWITCH(TRUE();
Patient[planned dismissal] > TIME(07;00;00) && Patient[planned dismissal] < TIME(15;00;00); TIME(15;00;00);
Patient[planned dismissal] > TIME(15;00;00) && Patient[planned dismissal] < TIME(23;00;00); TIME(23;00;00);
TIME(07;00;00))

the colum Patient[planned dismissal] is based on a conditional column. 

Patient[planned dismissal] =

Planned dismissal= IF(Patient[Gepland Ontslagdatum - Copy] = DATE(1900;1;1); NOW(); Patient[planned datetime])

In the image below, you can see the other columns. I've set the column Planned dismissal to 'time'.

So, to summarize: Timeframe is based on a calculated and a conditional column, changed to datatype 'time'. 

[MFelix]

Hi @RemiAnthonise ,

Is it possible to share a sample file?

As I refer based on my tests everything should match.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @RemiAnthonise ,

The question is about the data although you see the hours only the data behind is date so you need to get onlythe time part of Patient[Gepland ontslagtijd aangepast] column, redo your calculation to:

Tijdsblok ontslag =
VAR Hour_Part =
    TIME ( HOUR ( Patient[Gepland ontslagtijd aangepast] ); MINUTE ( Patient[Gepland ontslagtijd aangepast] ); SECOND ( Patient[Gepland ontslagtijd aangepast] ) )
RETURN
    SWITCH (
        TRUE ();
        Hour_Part > TIME ( 07; 00; 00 )
            && Hour_Part < TIME ( 15; 00; 00 ); TIME ( 15; 00; 00 );
        Hour_Part > TIME ( 15; 00; 00 )
            && Hour_Part < TIME ( 23; 00; 00 ); TIME ( 23; 00; 00 );
        TIME ( 07; 00; 00 )
    )

Gives the result below:

I'm not addin the PBIX since it tell me it was done on a previous version so if I send it out to you it will not open.

Regards,

MFelix

[RemiAnthonise]

Thanks a lot for your help, @MFelix .

[RemiAnthonise]

Hi @MFelix , here is my sample file.

Thanks in advance!