Forum Discussion

turbea's avatar
turbea
New Member
9 years ago
Solved

Calculating date/time difference

 

 

 

 

 

Hello,

 

I would like to calculate the difference between 2 date/time value (one is an additional column, the other is a measure). I need to see the result in days, but in decimal number.

If I just calculate the difference this way, I will get a rounded value as a result:

InvEntryTime = AIRActionHistory[ScanImportDate].[Day] - AIRActionHistory[FirstActionDate].[Day]

As you can see on the attached printscreen, I got 6 days, but I would like to see 6.44 days.

 

How I should calculate the difference to get decimal number as a result?

 

Many thanks!

 

 

Need Help
  • Back2Basics's avatar
    Back2Basics
    9 years ago

    Try this

     

     

    New column = (AIRActionHistory[ScanImportDate]-AIRActionHistory[FirstActionDate])+(((HOUR(AIRActionHistory[ScanImportDate])-hour(AIRActionHistory[FirstActionDate]))+(((MINUTE(AIRActionHistory[ScanImportDate])-MINUTE(AIRActionHistory[FirstActionDate]))/60))/24)

     

    I dont think you need the extra .[Date] at the end of things

6 Replies

  • Back2Basics's avatar
    Back2Basics
    Resolver I
    9 years ago

    I think this would work

     

    New column = value(AIRActionHistory[ScanImportDate].[Date])-value(AIRActionHistory[FirstActionDate].[Date])+(((HOUR(AIRActionHistory[ScanImportDate])-hour(AIRActionHistory[FirstActionDate].[Date]))+((MINUTE(AIRActionHistory[ScanImportDate])-MINUTE(AIRActionHistory[FirstActionDate].[Date]))/60))/24)

     

    I've not included seconds as I suspect they wil have little impact upon the decimal. If you were interested in including that it would be the same principle, just taking one from the other and dividing by 60 again - but you'll want to include it before the "/24" as that it becomes part of the decimal

    • turbea's avatar
      turbea
      New Member
      9 years ago

      Unfortunately I got 6.78 as result instead of 6.44.

      • Back2Basics's avatar
        Back2Basics
        Resolver I
        9 years ago

        Try this

         

         

        New column = (AIRActionHistory[ScanImportDate]-AIRActionHistory[FirstActionDate])+(((HOUR(AIRActionHistory[ScanImportDate])-hour(AIRActionHistory[FirstActionDate]))+(((MINUTE(AIRActionHistory[ScanImportDate])-MINUTE(AIRActionHistory[FirstActionDate]))/60))/24)

         

        I dont think you need the extra .[Date] at the end of things

  • Azunai's avatar
    Azunai
    Advocate I
    9 years ago

    Hi!

     

    I think, that may help:

     

    InvEntryTime = FORMAT(AIRActionHistory[ScanImportDate] - AIRActionHistory[FirstActionDate],"##.##")