Forum Discussion

SantoshKumar's avatar
SantoshKumar
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago
Solved

Hours Calculation result after excluding the Days

Hi,  I need to Modify Hours Column in My table.  Currently i have two Columns 1. Days 2. Hours as shown in the pic below. Hours value at present is Total Hours  but my requirement is to get Ho...
Need Help
Tips and Tricks
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi SantoshKumar,

     

    Here we can use MOD function to work on it.

     

    Hours = IF(Table1[Days]=1,0,MOD(DATEDIFF(Table1[StartTime],Table1[EndTime],HOUR),24))

     

    Regards,

    Frank

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Try:

 

Hours = 
IF([Days]>0,0,DATEDIFF...)

So, just plug in your formula for the FALSE condition.

  • SantoshKumar's avatar
    SantoshKumar
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Thanks Greg_Deckler

     

    i was able to get the details in sql when i providde my query as:  DateDiff(Hour,StartTrgTime,endTrgTime)%24

    when it comes to PB_query  - i have below option 

    DATEDIFF(BABugs[Days],BABugs[Hours],HOUR) and it results are zero but i need remainder as my result.
     could you help me on it.