Number of days between two Date/time dates

[nleuck]

Hello All,

I could some help in figure out how to find the number of days between two Date/time dates. For example, if I have a CreatedDate of 08/17/2020 3:13:00PM and a ResolvedDate of 08/18/2020 10:05:00AM, I would expect the number of days would be zero becasue a full 24-hour day has not been completed. When I use my formula I get 1 day. Here is my formula:

# of Days = 1 * (Table1[ResolvedDate] - Table1[CreatedDate])

Any help would be greatly appreciated.

Thanks!

[v-xicai]

Hi @nleuck ,

You may enter into Query Editor, go to Add column -> Custom column, type codes like below.

=Duration.Days(Duration.From([ResolvedDate]-[CreatedDate]))

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]

Hi @nleuck 

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), SECOND )

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), MINUTE )

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), HOUR )

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), DAY )  // This is what  you need.

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), WEEK )

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), MONTH )

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), QUARTER )

DATEDIFF(MIN( Calendar[Date] ), MAX( Calendar[Date]), YEAR )

[Anonymous]

HI @nleuck 

# of Days  = DATEDIFF(Table1[CreatedDate], Table1[ResolvedDate], DAY )  

[nleuck]

@Anonymous 

It seems to work for most but not all. For example, I have a CreatedDate of 08/17/2020 4:57:00PM and a ResolvedDate of 08/18/8:47:00AM. It shows # of Days as 1 when it should be zero. That's not a complete full 24-hour cycle.

[Anonymous · Accepted solution]

Hi @nleuck 

If the data needs to use the hour as finding dates(between any two past dates too) then I would use the below query.

# of Days  = FLOOR(DIVIDE(DATEDIFF(Table1[CreatedDate], Table1[ResolvedDate], HOUR),24),1)

[harshnathani]

Hi @nleuck ,

Try
IF( DATEDIFF(DATE1, Date 2, HOUR) <24, 0 , DATEDIFF(Date1,DATE2, DAY)

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[nleuck]

@harshnathani 

Almost. I have one with a CreatedDate of 08/17/2020 4:19:00PM and a Resolved Date is blank. At 3:50PM I refreshed my data and # of Days was 2 which is wrong because 2 full days have not passed. At 4:23PM I did another refresh and # of Days was also 2 which I would expect.

[Ajinkya369]

Hi @nleuck ,

Please share sample data.

Thank you