Calculating Time Difference in InfoPath Form


This article describe a custom method for time difference based on a custom combo box used for hours and minutes input.

You can download the XSN file from https://1drv.ms/u/s!ApGubfWFh8Nuq5o3q77TRbTkVX634w

timesheet

This form is designated to calculate the time difference between end hour and minute and start hour and start minute.

The form contains a XML resource file used to populate the combo box for hours and minutes:

Structure:

<Hour>
    <HValue>9</HValue>
    <HDisplay>09</HDisplay>
    <HType>R</HType>
    <MValue>0</MValue>
    <MDisplay>00</MDisplay>
</Hour>

 

  • HValue – Integer from 0 to 23
  • HDisplay – Text, 00, 01 to 23
  • HType – R – Regular, ABH – After Business Hours, N – Night hours
  • MValue – Integer from 0 to 59
  • MDisplay – Text, 00, 01 to 59

Worked hours and minutes are calculated using xPath 1.0 (details in article IF-THEN-ELSE in xPath)

For H Diff (worked hours) formula is:

concat(substring(concat(substring(../my:EndH – ../my:StartH, 1, (../my:EndM – ../my:StartM >= 0) * string-length(../my:EndH – ../my:StartH)), substring(../my:EndH – ../my:StartH – 1, 1, (not(../my:EndM – ../my:StartM >= 0)) * string-length(../my:EndH – ../my:StartH – 1))), 1, (../my:EndH – ../my:StartH >= 0) * string-length(concat(substring(../my:EndH – ../my:StartH, 1, (../my:EndM – ../my:StartM >= 0) * string-length(../my:EndH – ../my:StartH)), substring(../my:EndH – ../my:StartH – 1, 1, (not(../my:EndM – ../my:StartM >= 0)) * string-length(../my:EndH – ../my:StartH – 1))))), substring(concat(substring(../my:EndH – ../my:StartH + 24, 1, (../my:EndM – ../my:StartM >= 0) * string-length(../my:EndH – ../my:StartH + 24)), substring(../my:EndH – ../my:StartH + 23, 1, (not(../my:EndM – ../my:StartM >= 0)) * string-length(../my:EndH – ../my:StartH + 23))), 1, (not(../my:EndH – ../my:StartH >= 0)) * string-length(concat(substring(../my:EndH – ../my:StartH + 24, 1, (../my:EndM – ../my:StartM >= 0) * string-length(../my:EndH – ../my:StartH + 24)), substring(../my:EndH – ../my:StartH + 23, 1, (not(../my:EndM – ../my:StartM >= 0)) * string-length(../my:EndH – ../my:StartH + 23)))))) – ../my:LunchBreak

  • LunchBreak – is 1 when is checked and 0 when is unchecked.

For number of minutes (M Diff) the formula is:

concat(substring(../my:EndM – ../my:StartM, 1, (../my:EndM – ../my:StartM >= 0) * string-length(../my:EndM – ../my:StartM)), substring(../my:EndM – ../my:StartM + 60, 1, (not(../my:EndM – ../my:StartM >= 0)) * string-length(../my:EndM – ../my:StartM + 60)))

Also, in the form is implemented a validation rules for cases when the hour is the same and start minutes are greater than end minutes.

ValidationRule

This rule is used also to deactivate submit button.

Future improvements: Add AM/PM format for time differences.

Hope it helps!

Anunțuri

Lasă un răspuns

Completează mai jos detaliile tale sau dă clic pe un icon pentru a te autentifica:

Logo WordPress.com

Comentezi folosind contul tău WordPress.com. Dezautentificare / Schimbă )

Poză Twitter

Comentezi folosind contul tău Twitter. Dezautentificare / Schimbă )

Fotografie Facebook

Comentezi folosind contul tău Facebook. Dezautentificare / Schimbă )

Fotografie Google+

Comentezi folosind contul tău Google+. Dezautentificare / Schimbă )

Conectare la %s