Calculate duration of work, according to date range

[thoe0059]

Hello everyone,

I have an objective to calculate the time in hours, on different dates. 

I doesn't matter what activities happen on that day, and if there is an overlap it should not be accounted for. I simply need to know how many hours went from starting working that day until the work stopped. 

Also it is important that the work is sorted into categories of the work location. 

A visual representation of this is added in my example workflow, along with the desired output.

I hope you guys can help me in the right direction.

Kind regards,

Thøger

[Emil_Kos]

Hi @thoe0059,

In order to add underlying data you need to export the workflows using export workflow option:

Without that, we will not see underlying data. 

[MarqueeCrew]

@thoe0059 

I think datetimediff() is your friend:

DateTimeDiff

DateTimeDiff(dt1,dt2,u): Subtracts the second argument from the first and returns it as an integer difference. The duration is returned as a number, not a string, in the specified time units.

Parameters

dt: Date-time data expressed as a selected column or a specified date-time value between quotes.

u: Date-time unit, specified between quotes: years, months, days, hours, minutes, or seconds.

cheers,

 mark

[thoe0059]

Thanks @Emil_Kos , I hope this export includes the data 🙂 

[thoe0059]

@MarqueeCrew 

Thanks a lot for the reply. 

When using the DateTimeDiff function, i would want to find the duration between the first activity and the last activity on a given date. The total hours in that timeframe would be the result. But how do i find out evaluate what the first and last activity was?

[MarqueeCrew]

@thoe0059 ,

 if activity is a datetime field, I'd first create a formula of activity date. 

Then take the data into a summarize

group by activity date 

min activity

max activity

 then you can datetimediff() the values. 

you'll have data on each date.  If you add other group by fields, it could be by store/plant/employee.  

does that make sense?

 Cheers,

 mark

[thoe0059]

@MarqueeCrew 

Thanks for replying so promptly!

I'm not sure what you mean by creating a formula of activity date, could you please elaborate a bit?

Kind regards, 

Thøger

[MarqueeCrew]

Just the date.  

left([activity],10) as a date field.  

if you want to find mine and Mac from a date. 

cheers,

 mark

[thoe0059]

@MarqueeCrew 

Hi Mark, 

I don't like to be the idiot in the room, but i still don't quite get what you're writing. 

Can you explain your reply in context of my workflow? I'm sorry for the inconvenience, but i'm an Alteryx noob 😄

Kind regards

[Emil_Kos · Accepted solution]

Hi @thoe0059,

I have prepared a workflow for you. In order to prepare it, I have created a proper date-time formula and afterwards, I group the data using two summarize tools. The last step is using date-time diff function to count the difference. 

I am not sure but it looks like your duration has different results than mine not sure why is that. I am assuming that you filled some numbers just as an example.

My output:

Your output:

If this was helpful please mark my post as a solution!