Calculating date/time difference

[turbea]

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!

[Azunai]

Hi!

I think, that may help:

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

[Back2Basics]

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]

Unfortunately I got 6.78 as result instead of 6.44.

[Back2Basics · Accepted solution]

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

[turbea]

Yes, you are right. I dont need the extra .[Date] thing.

Many thanks!

[turbea]

With the second option I can see the decimals, but they are 00 only. It seems the formula cannot use the time in the date/time format to calculate. Only takes the date.