using "if" function extract time

[Anonymous]

Hello, I am trying to extract hours from my time column which contains hours in a  hh:mm: SS format. I want to extract hours when the time is between 9:30 to 10:00 I want to get the value "9:30 - 10:00" otherwise I want to get the hours.

I used the following DAX formula, but it didn't work. 

New column = IF([time]>=09:30:00; IF([time]<=10:00:00; "9:30 -10:00" ;HOUR([Time]))

I get the error,  the syntax for ":" is incorrect

when I change the formula to

New Column = IF([Time]>=("09:30:00")&&[Time]<=("10:00:00");"9:30 -10:00";HOUR([Time]))

I get the error Dax comparision do not support comparing values of type date with text, but my time column has a datatype time.

[v-piga-msft · Accepted solution]

Hi @Anonymous,

For your scenario, I create a data sample as example. Assuming that you have Time column like this.

Time

8/2/2018 9:28:20

8/1/2018 9:30:00

8/1/2018 9:30:05

8/1/2018 9:45:20

8/1/2018 10:00:00

8/1/2018 10:01:00

8/2/2018 10:00:05

You could create a calculated column with the formula below.

Column =
VAR h =
    HOUR ( 'Table'[Time] )
VAR m =
    MINUTE ( 'Table'[Time] )
VAR s =
    SECOND ( 'Table'[Time] )
RETURN
    IF (
        h >= 9
            && m >= 30,
        'Table'[Time],
        IF ( h <= 10 && m = 0 && s = 0, 'Table'[Time] )
    )

Then you will get the output like this:

Best  Regards,

Cherry