Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

using "if" function extract time

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.

Show and Tell
Tips and Tricks
  • v-piga-msft's avatar
    v-piga-msft
    8 years ago

    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

1 Reply

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    8 years ago

    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