I’ve read a lot of posts about this topic but not found a solution which works for me, this should be a fairly simple calculation?
My workflow takes the end time - start time but the resulting total is a strange long number? Why isn’t this giving me the answer 4 minutes?
Times are stores in the DB as Dates and the total is stored as a number.
If you use ‘format as minutes’ it will show the difference in minutes…
What are you currently using?
Also, the number shouldn’t be negative… so it looks like you’re doing the calculation wrong
Update: in fact the number you’ve got there (assuming it is formatted as minutes) seems to be exactly the start of the Unix epoch minus the current date/time - which should give you a clue as to what’s going on…
So it looks like you’re doing something wrong in your expression (either on the workflow that stores it, or on the element that displays it). Some screenshots of your expression/calculation should help here (although i can already guess what you’re doing wrong from the result you’re seeing)
Hey @adamhholmes, thanks for helping. I am keeping everything as simple as possible so the stop button records the stop time as well as a recorded (total) time.
This total figure is then displayed on the group;
I also was confused by the fact that the number is negative. I found that if I make the recorded time start time - end time it is not negative, but this makes no sense, surely the time elapsed should be the opposite, ie.end time - start time?
How and where are you setting the stop time for the shift?
The stop button triggers the above workflow which saves the Current date/time to the database field.
The resulting data looks like this in my database;
Ahh I see what you’re doing wrong… you’re trying to set the Shift’s End Time in the same workflow action as you’re calculating the total time…
That won’t work (and obviously isn’t working)… you can’t reference a value that you haven’t yet set…
And if you try to, it will be empty, which is exactly what’s happening here… (you should be able to see that for yourself using the debugger)…
So the calculation you’re actually doing is the end time (which is empty, and therefor 0 - or 1st Jan 1970 00:00) minus the start time, which will give you a total number of -27503102 minutes (which is correct given the calculation).
To make it work the way you need it to you need to store the value for the stop time first (before referencing it in the calculation).
So, either add an extra step to the workflow to set the stop time on the Shift first, or (a better option as it requires less database interaction) use a custom state to store the end time, then use that value to store both the end time on the Shift, and for the calculation of total time.
Thanks @adamhholmes that totally makes sense. That is interesting that the default earliest date is 1st Jan 1970 00:00!
I have resolved the issue using your first example of adding an extra step to the workflow.
With regards to the other option, I can’t quite see how this would require less database interaction as I still need to save the “Recorded time” to the DB?
One issue which I will need to think about is how to display the results in hours and minutes so the user sees 01:12 or 1 hr 12 mins instead of 72 minutes. I could set a condition which divides the total time by 60 when the total time is more than 60 minutes to get the hours but I’ll lose the remainder minutes, I am still thinking about this but want to resolve the other issues first!
There are a few ways to do it…
One is just to calculate the hours and minutes manually (dividing minutes by 60, and using ‘floor’ and ‘modulo 60’ operators to get the minutes), and use some conditional formatting to display the time.
Another is to use an arbitrary date of 1st Jan 1970 00:00 plus the number of minutes in question- then just format it as H:MM (works great as long as the time you’re adding is less than 24 hours)
Or you can try the SecondsToDate plugin.
Thanks for the information @adamhholmes that is so useful. It is possible that my users will record a time more than 24 hours so I will look at the other options, the plugin or using the ‘floor’ and ‘modulo 60’ operators.
Thanks to @adamhholmes for providing the answer to this threads question, I’ve marked this as the solution because it’s an expansion on the answer and thought it useful if people are searching for how to calculate time elapsed between start and end dates.
For my use case I decided to calculate the hours and minutes manually and display the result in a 1 hr 23 mins format as well as 01:23 format. I will show how I did both.
1 hr 23 mins format
To display hours
To display minutes
Displaying the result in a [01:23] format.
This was slightly trickier because I needed to ensure the preceding zero shows when there is less than 10 hours. I achieved this with a bit of trial and error and this may well be incorrect, but it seems to work.
To display hours
To display minutes
