Calculate duration of work, according to date range
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks @Emil_Kos , I hope this export includes the data 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Just the date.
left([activity],10) as a date field.
if you want to find mine and Mac from a date.
cheers,
mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
