Forum Discussion

Jolyon's avatar
Jolyon
Helper III
9 years ago
Solved

Calculation with date/hours?

Hello dear community, I kindly ask for your help.   I have a table with different art of orders and their Open and Closed Date(in format dd: mm: yyyy hh:mm:ss). 1)Now I want to calculate th...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    9 years ago

    Jolyon

     

    Please check a formula as below.

     

    Hours Between =
VAR elapsedHours =
    DATEDIFF ( YourTable[Open Date], YourTable[Closed Date], HOUR )
RETURN
    IF (
        ISBLANK ( YourTable[Closed Date] ),
        BLANK (),
        SWITCH (
            TRUE (),
            elapsedHours <= 12, "12",
            elapsedHours <= 24, "24",
            elapsedHours <= 36, "36",
            elapsedHours <= 48, "48",
            elapsedHours <= 72, "72",
            elapsedHours <= 240, "240",
            "Over 240"
        )
    )

     

     

    If it answers your question, please accept it as solution to close this thread. For any question, feel free to post.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

For difference in dates in hours, try playing with the below logic:

Var = FORMAT( [Closed Date] - Table1[Open Date], "DD" ) * 24 + FORMAT( [Closed Date] - Table1[Open Date], "hh" ) 

 

As for Banding, try playing with the below approach:

 

=
SWITCH (
    TRUE (),
    [Time between] < 12, "<12",
    [Time between] >= 12 && [Time between] <= 24, "12-> 24",
    "24+"
)

 

The switch statement can go on forever so put in as many bands as you'd like!

 

There is a better approach than this by levaerging a banding table by the way.  It is quite an advanced technique and can cause some issues with relationships unless you're comfortable with data modelling.  If you're interested I can post details.