3

I have three editable date/time fields which the first two is (field1 and field2), style: Calendar/time control. Both of them are showing the time: hour and minutes, eg: 15:51.

The third field also (editable) which I want to show the difference between field1 and field2.

Eg: If field1 is 14:41 and field2 is 14:30, then field3 = 00:11. I've tried field1-field2 but isn't working. The form has automatic refresh fields property. Thanks!

Reinhaa
  • 195
  • 1
  • 17
Florin M.
  • 2,159
  • 4
  • 39
  • 97
  • If your fields are storing date/time values, the formula does not pay any attention to how you are displaying them. When you subtract one date or time from another, you always get the answer in seconds. That is why we have to do some extra work to get days, hours or minutes. – Phil M Jones Sep 14 '12 at 08:36
  • OK, I see where I have gone wrong. I need @Modulo on the second value to get rid of the hours part. @If(field1 = "" | field2 = ""; "" ; @Right("00" + @Text(@Integer((field1-field2)/3600)); 2) + ":" + @Right("00" + @Text(@Modulo(@Integer((field1-field2)/60);60)); 2)) – Phil M Jones Sep 14 '12 at 11:24

3 Answers3

3

Your third field needs to be computed, not editable.

If it HAS to be editable for some reason, and you want it to update when the other two fields are changed, do this:

Create a new field and make it computed-for-display and hidden. Give it a formula like this

@If(field1=null | field2=null; @Return(""); "");
seconds := field1-field2;
hours := @Integer(seconds/3600);
minutes := @Modulo(@Integer(seconds/60); 60);
output := @Right("00" + @Text(hours); 2) + ":" + @Right("00" + @Text(minutes); 2);
@setfield("field3"; output);
@Command([ViewRefreshFields]);
""

Phil

Phil M Jones
  • 825
  • 5
  • 15
1

I wrote this code, much easier...

Fields 'StartTime' and 'EndTime': Type Date/Time, use Calendar/Time control, set it to only display time. Check the property "Run Exiting/OnChange events after value changes". The Exiting event should look like this:

Sub Exiting(Source As Field)
    Call UpdateDuration()
End Sub

Field 'Duration': Editable text field, but hidden.

Field 'dspDuration': Computed for display text field. Value is just "Duration" (no quotes).

Then add the following code to the forms Global section:

Sub UpdateDuration()
    Dim ws As New NotesUIWorkspace
    Dim uidoc As NotesUIDocument
    Dim starttime As NotesDateTime
    Dim endtime As NotesDateTime
    Dim duration As Integer

    Set uidoc = ws.CurrentDocument
    '*** Exit if not both times are entered
    If uidoc.FieldGetText("StartTime") = "" Then
        Exit Sub
    Elseif uidoc.FieldGetText("StartTime") = "" Then
        Exit Sub        
    End If
    '*** Calculate duration in seconds and update field
    Set starttime = New NotesDateTime( uidoc.FieldGetText("StartTime") )
    Set endtime = New NotesDateTime( uidoc.FieldGetText("EndTime") )
    duration = endtime.TimeDifference( starttime )
    Call uidoc.FieldSetText("Duration", Cstr(duration) )
    Call uidoc.Refresh()
End Sub

That's it. Easy, isn't it? If you want to modify the output (duration), you can easily do that, perhaps change it into minutes by diving it by 60.

Screenshot

Karl-Henry Martinsson
  • 2,770
  • 15
  • 25
  • You are welcome, Florin. In some cases, you want to use Lotusscript instead of Formula. As a Notes programmer, you need to know both, and you will also have some additional benefits if you know Javascript (as it can be used in the client as well, in some places where Formula and/or Lotusscript cannot be used). Since Lotusscript is based on Visual Basic, it is very easy to learn. The "tricky" part is to learn the DOM classes, but the online help is very good, and you find many examples there. Learning the classes will also give you a better understanding of the Notes/Domino platform. – Karl-Henry Martinsson Sep 17 '12 at 14:56
0

Make sure you are getting the difference between two date time fields. If you need to, you can use the @TextToTime formula to convert text to a datetime type.

Then just subtract the first date from the second date and you'll get the difference in seconds.

Then divide that by 60 to get the difference in minutes.

Ken Pespisa
  • 21,989
  • 3
  • 55
  • 63