I have a task column with different statuses and their timestamps (data coming from Elastic). I would like to calculate the duration by taking the timestamp of a complete task and subtract the timestamp of the claimed task to get a duration in minutes. The difficulty is the current timestamp format is in mm/dd/yyyy @ hh:mm:ss so I can't figure out how to convert and calculate the duration.
Please see the attached excel for the current data layout. For example batch name 1313278 would have a duration of 20.66 mins
@Sky L.
Hi, well the firt issue with the date was not a problem, just in the connection page, change it to date time:
After that you need to create a few of calculated fields:
Claimed Date
{FIXED [Batch Name]:MIN(IF [Task]="claimed" THEN [Timestamp] END)}
Complete Date
{FIXED [Batch Name]:MIN(IF [Task]="complete" THEN [Timestamp] END)}
Duration:
DATEDIFF("second",[Claimed Date],[Complete Date])/60
Attached you will find the workbook.
If this post resolves the question "Select as Best" or if it assists in resolving the question, please "Upvote". This will help other users find the same answer/resolution and help community keep track of answered questions. Thank you.
Regards,
Diego
