10 Replies Latest reply on Jul 29, 2018 3:02 AM by Mahfooj Khan

# How to calculate days, hours and minutes using only start date and end date?

Hi Tableaurs,

I have a start date and end date which is START DATE 20170429 12:40:00  and END DATE 20170502 14:20:00 .

How to calculate using calculated field in order to get exactly 3 days, 1 hour, 40 minutes and 0 seconds.

[

• ###### 1. Re: How to calculate days, hours and minutes using only start date and end date?

Try this,

Create a calculated field to get the time difference in second from both the dates.

Seconds:

DATEDIFF('second',[Start Date],[End Date])

Now Create another field to get the

dd:hh:mm:ss:

IIF([Seconds] % 60 == 60,0,[Seconds] % 60)// seconds

+ IIF(INT([Seconds]/60) %60 == 60, 0, INT([Seconds]/60) %60) * 100 //minutes

+ IIF(INT([Seconds]/3600) % 24 == 0, 0, INT([Seconds]/3600) % 24) * 10000 //hours

+ INT([Seconds]/86400) * 1000000 // days

Once done then change the number format of the above field

Hope this help.

Mahfooj

• ###### 2. Re: How to calculate days, hours and minutes using only start date and end date?

Hi Mahfooj,

Thanks for the reply. I am not getting correct conversion. I did what you have typed above..

this is what i am getting. its calculating wrong.. supposed to get

## 8 days, 0 hours, 25 minutes and 0 seconds for first row.. pls correct me

• ###### 3. Re: How to calculate days, hours and minutes using only start date and end date?

I'm getting the expected output.

Mahfooj

• ###### 4. Re: How to calculate days, hours and minutes using only start date and end date?

Hi Mahfooj,

Still getting wrong conversion. Kindly see the attached document..

• ###### 5. Re: How to calculate days, hours and minutes using only start date and end date?

Can you attache the .twbx (packaged) workbook once again?

• ###### 6. Re: How to calculate days, hours and minutes using only start date and end date?

Hi Mahfooj,

I figure out what went wrong, in my data source there are two dates (start and end) are repeating the same Thus, its summing up those 2 dates ended up 16 hours 50 mins.

• ###### 7. Re: How to calculate days, hours and minutes using only start date and end date?

Hi Mahfooj,

Attached are twbx extension

• ###### 8. Re: How to calculate days, hours and minutes using only start date and end date?

Ohkay

However if you take avg() you might get the correct result too

Kindly mark my answer as correct so that this post gets hide from unanswered list

• ###### 9. Re: How to calculate days, hours and minutes using only start date and end date?

Thankyou.

• ###### 10. Re: How to calculate days, hours and minutes using only start date and end date?

You're welcome