cancel
Showing results for
Did you mean:
Highlighted
New Member

## 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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III

## Re: Calculating date/time difference

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 6
Highlighted
Helper III

## Re: Calculating date/time difference

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

Highlighted
Frequent Visitor

## Re: Calculating date/time difference

Hi!

I think, that may help:

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

Highlighted
New Member

## Re: Calculating date/time difference

Unfortunately I got 6.78 as result instead of 6.44.

Highlighted
New Member

## Re: Calculating date/time difference

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.

Highlighted
Helper III

## Re: Calculating date/time difference

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

Highlighted
New Member

## Re: Calculating date/time difference

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

Many thanks!

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors