General

Re: Difference between two 'Date and Time Picker' controls

Snowman — Thu, 20 Nov 2014 18:33:01 GMT

Hi Jimmy or anyone else who may be able to help me,

I'm using the formula below; however, getting an "Expected value type, Actual value: name error". Any idea what I am doing wrong? Thank you.

concat(substring-before(.,";")+substring-after(.,";")*((translate(xdDate:AddDays(../my:startDate,substring-before(.,";")),"-","")<translate(../my:endDate,"-",""))*2-1),";",substring-after(.,";")div2)

SNOWMAN

Re: Difference between two 'Date and Time Picker' controls

mohamad morshed — Mon, 29 Oct 2012 10:02:47 GMT

I used it, It was Really useful, Thanks a lot

Re: Difference between two 'Date and Time Picker' controls

nbarnes — Sat, 05 Nov 2011 18:27:39 GMT

Thanks for your quick reply Jimmy,

I've already got the date working so I just need to add your bit for the time. Looks like I might be making a few forms with dates as a central theme so I'll definitely look into qRules.

Thanks again,

Nick

Re: Difference between two 'Date and Time Picker' controls

Jimmy — Sat, 05 Nov 2011 18:18:12 GMT

I hope I didn't get your hopes up too much because I don't know of a good way to account for business hours and holidays. I was just making sure that wasn't a requirement.

What you can do to get the raw difference is:

Use the trick that's been floating around here to get the number of days between the two dates.
Use my formula to get the difference between the two times (if the time on the start date is earlier in the day than the time on the end date, the result for this will be negative, but this is ok).
Add 1 and 2 together (making sure to convert them to the same units first).

That should give you the result you need. I'll also point out that qRules provides an extremely simple command, DateDiff, for determining the difference between two datetimes, along with a whole lot of other cool stuff, and I recommend checking that out if you're looking to get more out of your InfoPath experience.

Re: Difference between two 'Date and Time Picker' controls

nbarnes — Sat, 05 Nov 2011 17:58:50 GMT

Within business hours would be great but thats well beyond my Infopath knowledge so I hadnt even considered it!

If you know how, then that would be awesome but I'm also happy with just raw differences.

Re: Difference between two 'Date and Time Picker' controls

Jimmy — Sat, 05 Nov 2011 17:53:26 GMT

And are you looking for just a raw difference between two datetimes? Do you need to take into account work hours, weekends and holidays?

Re: Difference between two 'Date and Time Picker' controls

nbarnes — Sat, 05 Nov 2011 17:00:46 GMT

Hi Jimmy,

Thanks for getting back to me. The difference between the two dates/times will vary condiserably, some forms might be returned within a couple of hours but others might take several days.

Nick

Re: Difference between two 'Date and Time Picker' controls

Jimmy — Sat, 05 Nov 2011 16:47:07 GMT

Will both of the times always be within the same day? If so, you can use this formula to get the number of minutes between two times:

60 * (substring(laterTime, 12, 2) - substring(earlierTime, 12, 2)) + (substring(laterTime, 15, 2) - substring(earlierTime, 15, 2)) + (substring(laterTime, 18, 2) - substring(earlierTime, 18, 2)) div 60

Is that sufficient for what you're looking for?

Difference between two 'Date and Time Picker' controls

nbarnes — Sat, 05 Nov 2011 14:40:29 GMT

Hello everyone,

Im creating a web-enabled Infopath 2010 form which will be used to enter details of IT Support tickets. The form has two 'Date and Time Picker' controls:

TimeRaised
TimeRepaired

I'd like the difference between these two fields to be displayed in a read-only text box titled TimeToResolve, so that admin can easily identify the most troublesome support tickets. I've found lots of resources covering Date Picker controls, however I cant find anything about calculating differences between two Date and Time Picker field?

Can anyone help?

Thanks!

Nick