none
INFOPATH: how can I calculate hours between 2 date fields in a list form

    Question

  • I am trying to calculate the number of hours between 2 date/time fields in a list form.

    If I do this on a list view level with a calculated column, the column is not available for column summaries. Therefor I intend to use a regular number column and calculate the hours in an InfoPath form. Obviously the formula for calculated fields in list columns will not work in InfoPath.

    Addition: I tried DateField1-DateField2, but the result is NaN...

    Suggestions? 

    Thx


    Iko
    • Edited by Iko Knyp Thursday, December 02, 2010 7:15 PM update
    Thursday, December 02, 2010 7:13 PM

Answers

All replies

  • Unfortunately you are going to have to split the pieces up (month, day, year) and convert them to numbers and do the math.

    For example for days

    24 * (number(substring(field1, 9, 2)) - number(substring(field2, 9, 2)))

    Obviously, this gets a little tricky. If you can use code behind you can covert the date to a number and then convert that number to hours but then it would have to be an administrator approved form.
    Hope that helps,
    SharePointNinja

    Thursday, December 02, 2010 7:53 PM
  • Thanks for putting this up. Hard to believe ... ;-)

    Iko
    Thursday, December 02, 2010 8:39 PM
  • It is pretty nasty, but as far as I know that is the only way to do it and have it work like a normal infopath form.
    Thursday, December 02, 2010 8:45 PM
  • The nasty stuff comes with months having between 28 and 31 days, and leap years having 366 days... starting to wonder if this can even be expressed in a formula?

    Probably by putting all accumulated days for each month per month into a string, and then parsing it out again...

     


    Iko
    • Edited by Iko Knyp Thursday, December 02, 2010 9:34 PM update
    Thursday, December 02, 2010 9:27 PM
  • I think it works: ;-) lovely....

     

    ((number(substring(Time Out, 15, 2)) / 60) + number(substring(Time Out, 12, 2)) 
    + (number(substring(Time Out, 9, 2)) * 24) + 
    (number(substring("000031059090120151181212243273304334", ((number(substring(Time Out, 6, 2)) - 1) * 3) + 1, 3)) 
    * 24) + (number(substring(Time Out, 3, 2)) * 365 * 24)) - ((number(substring(Time In, 15, 2)) / 60) 
    + number(substring(Time In, 12, 2)) + (number(substring(Time In, 9, 2)) * 24) + 
    (number(substring("000031059090120151181212243273304334", ((number(substring(Time In, 6, 2)) - 1) * 3) + 1, 3)) 
    * 24) + (number(substring(Time In, 3, 2)) * 365 * 24))


    Iko
    Thursday, December 02, 2010 11:09 PM
  • Friday, December 03, 2010 3:07 AM
  • Interesting as well, but does not give you hours (multiplying by 24 won't do it). 
    Iko
    Friday, December 03, 2010 3:36 AM
  • Nope, just days.  Why we can't subtract dates and times is beyond me.  I put it in as a request a while back, so hopefully it will make it into O15.
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Friday, December 03, 2010 3:48 AM
  • OK. I believe we've heared enough whining already :P

    Now here is the solution:

    http://alecpojidaev.wordpress.com/2008/12/30/infopath-codeless-programming-walkthrough-2/

    I added a new form in a new edition of my article.


    http://alecpojidaev.wordpress.com
    Monday, December 06, 2010 4:04 PM
  • Alex, I like your solution, but why do you have 2 time drop down fields?  How would I change it so that the time is free formatted similar to what the date and time picker already provides where the final results gives me the difference in hours AND minutes (in decimal format) instead of just hours?  As an example, if the difference is 1 hour and 15 minutes, how would I get 1.25 hours as the HoursDiff value?  Thanks for any advice!
    Wednesday, September 14, 2011 10:13 PM