8 Replies Latest reply on Aug 30, 2016 11:18 AM by Tracy Orwig

# Difference of hours between a set time and an end timestamp

I have data for a list of people showing their start and end times.  I need to find how many hours they worked after 9pm each day they have worked.  I have a filter that shows those who had a end timestamp after 9pm but I have yet figured out how to get a static time of 9pm to find the difference in the hours.  I am sure it's doable but I haven't yet figured out how to create a static timestamp of 9pm to subtract their end timestamp.

• ###### 1. Re: Difference of hours between a set time and an end timestamp

Hi, what format is the start and end time in?

Ex. 5:09, or is it a date as well?

An example from your data would help a lot.

• ###### 2. Re: Difference of hours between a set time and an end timestamp

This should create a time of 9:00 pm. Although if the times in your data are part of dates it may not jive.

DATEPARSE ("h:m", "21:00")

• ###### 3. Re: Difference of hours between a set time and an end timestamp

I’ve attached a workbook with the datetime fields.

I need to find the difference between 9pm and the punch end by day for those who stayed passed 9pm.

• ###### 4. Re: Difference of hours between a set time and an end timestamp

Hi Tracy!

This is such a good question. Got to refresh my date skills in Tableau (=

Using MAKETIME, I created that 9pm timestamp (MAKEDATETIME(([DatePunchEnd]),maketime(21,00,00)), and then took the DateDiff between that Calculation and the PunchEnd DateTime. (As a side note, I always take the DateDiff in minutes, then divide by 60 so you get the partial hours they worked as well).

See Sheet10 in attached.

Thanks!

• ###### 5. Re: Difference of hours between a set time and an end timestamp

This should give you minutes between 9:00pm and their punch time.

DATEDIFF('minute', DATEADD('hour', 21, [Date]), [Punch End])

DATEADD('hour', 21, [Date]) just takes the date and makes it 9:00pm that day, then datediff takes the difference between the punch.

• ###### 6. Re: Difference of hours between a set time and an end timestamp

for some reason the twbx will not open for me.

• ###### 7. Re: Difference of hours between a set time and an end timestamp

Hey Tracy,

What version of Tableau are you using? This was made in 9.3. I tried to open your initial workbook 9.0, but looks like it was made in a newer version.

• ###### 8. Re: Difference of hours between a set time and an end timestamp

Yes, it was made in 9.3.  I was able to take the formula that you provided in the text and apply it to my worksheet and I came up with the correct answer.  thank you very much!