Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Hours Subtraction

Hi there,   My formula (NETWORKDAYS("9/6/2020","30/06/2020", Holidays)-1)*10/24)+ TIME(18,0,0) - TIME(14:0:0))   Comes out at 6.416 on excel. When formatted to [h]:mm it displays as expected 154...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    Hi Anonymous ,

     

    It seems that the a time expression with TIME function is unsupported to calculate in DATESBETWEEN function, since it's syntax is like  DATESBETWEEN(<dates>, <start_date>, <end_date>) , which only accepts a date expression for the second and third argument.

     

    CALCULATE(SUM(vwDimDate[WorkingDayFlag]), DATESBETWEEN(vwDimDate[Date],MAX('Room Utilisation'[New Start Date]),

              [ParameterEndDate]))-1 *10/24 + TIME(18,0,0) - MAX('Room Utilisation'[Start Time, ])

     

    Best Regards,

    Amy 

     

    Community Support Team _ Amy

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak ,

 

Many thanks for that. Ive realised its this part of the calculation below.

 

TIME(18,0,0) - TIME(14,0,0)  
 
The result is coming out as 30/12/1899 04:00:00, and it wont let me change it to anything but date/time. Is there a way around this?
 
If the only way is to format to text, is there a way to do a calculation?
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi amitchandak ,

     

    In short I need to get from 6.416666667 to 154:00:00 ([h]:mm:ss), which excel does.

     

    Ive looked at many threads but nothing is equaling it.

     

    Thanks

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      Anonymous , I doubt you have 154 hours here. So you have create a text like that.

       

      6.6.416666667 *3600 Second  

      Now from here get hours min and sec using mod and quotient and create that string. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi amitchandak 

         

        Do you know how excel calculates using [h]:mm:ss as it gives me 154:00:00 but hh:mm gives 10:00