Calculate Difference Between Two times (HH:mm)

Resolved memotech
(@memotech)

2 years, 9 months ago

hello,

i need your help,
1) i want to show 2 datetime fields (start time and finish time) but without the long format dd/mm/yyyy, only the hours and the minutes

2) and of course calculate the difference between the finish and start times
What formula should I use please?

exemple :
Start time 02:15
finish time 03: 28
Result 01:13

Viewing 8 replies - 1 through 8 (of 8 total)

Plugin Author codepeople
(@codepeople)

2 years, 9 months ago
Hello @memotech

To hide the date component in the date/time fields, untick the “Show input field for the date” checkbox in their settings, and to activate the time components, tick the “Include time” checkbox.

Assuming the date/time fields are fieldname1 and fieldname2, insert a calculated field in the form and enter the equation:
```
(function(){
var o = DATEDIFF( fieldname1, fieldname2, 'hh:ii', 'h');
return CONCATENATE(o['hours'], ':', o['minutes']);
})()
```
Best regards.
Thread Starter memotech
(@memotech)

2 years, 9 months ago

thank you very much,its working

Is it possible to format the result like 00:00 (with the zero and not 0:0)

exemple :
Start time 03:43
Finish time 05:49
Actual result 2:6
More nice like 02:06
thanks
Plugin Author codepeople
(@codepeople)

2 years, 9 months ago
Hello @memotech

In this case, you can edit the equation as follows:
```
(function(){
var o = DATEDIFF( fieldname1, fieldname2, 'hh:ii', 'h');
return CONCATENATE( IF(o['hours']<10, '0', ''),o['hours'], ':', IF(o['minutes']<10, '0', ''),o['minutes']);
})()
```
Best regards.
Thread Starter memotech
(@memotech)

2 years, 9 months ago

ok thanks a lot

Thread Starter memotech
(@memotech)

2 years, 8 months ago

Hello again,

To continue in the same project, I would like to sum 2 times differences

Exemple :
– Morning Start Time, Morning Finish Time, the difference
– Afternoon Start Time, Afternoon Finish Time, the difference
– and the Sum of the 2 differences (see the photo)

the difficulty is that I can’t do any operation with the results calculated previously, they are concatenated values

Thank you for your support.
Plugin Author codepeople
(@codepeople)

2 years, 8 months ago
Hello @memotech

Actually, does not matter if they are concatenated. It is a text with time format. For can use exactly the same operation, but entering the names of the calculated fields:
```
(function(){
var o = DATEDIFF( fieldname1, fieldname2, 'hh:ii', 'h');
return CONCATENATE(o['hours'], ':', o['minutes']);
})()
```
Thread Starter memotech
(@memotech)

2 years, 8 months ago
Sorry, we misunderstood,
The function of calculating the difference between the 2 dates works very well.

Now I would like to sum two time differences. See photos and explanations above. I used :
```
DATETIMESUM(fieldname1,'h:i',TIMETODECIMAL(fieldname13,'h:i','i'), 'i')
```
But it does not work.
the fieldname1 and fieldname13 are the results of the formula you sent me

fieldname1 :
```
(function(){var o = DATEDIFF( fieldname4, fieldname3, 'hh:ii', 'h');
return CONCATENATE(o['hours'], ':', o['minutes']);
})()
```
fieldname3 :
```
(function(){
var o = DATEDIFF( fieldname12, fieldname11, 'hh:ii', 'h');
return CONCATENATE(o['hours'], ':', o['minutes']);
})()
```
fieldname15 :
```
DATETIMESUM(fieldname1,'h:i',TIMETODECIMAL(fieldname13,'h:i','i'), 'i')
```
is that more understandable?

thanks
Plugin Author codepeople
(@codepeople)

2 years, 8 months ago
Hello @memotech

If you want to add values in time format, the correct process would be to transform them to the same unit of measurement (such as minutes), add the result, and finally, transform the value to hours:minutes format.

For example, if the calculated fields are fieldname1 and fieldname2, respectively:
```
DECIMALTOTIME(SUM(TIMETODECIMAL(fieldname1,'h:i','i'), TIMETODECIMAL(fieldname2,'h:i','i')), 'i', 'h:i')
```
Best regards.

Viewing 8 replies - 1 through 8 (of 8 total)

The topic ‘Calculate Difference Between Two times (HH:mm)’ is closed to new replies.

Tags