General

Re: Function to round time UP to nearest hour?

Hilary Stoupa — Thu, 10 Sep 2015 14:44:30 GMT

I'm not sure - I don't think the round function works like that... unless maybe you changed the ":" to a decimal....?

Re: Function to round time UP to nearest hour?

Sailor — Thu, 10 Sep 2015 13:17:41 GMT

Thank you Hilary. I appreciate you looking. ElapsedTime is indeed a default value. And to be 100% honest these formulas are way beyond me too! I struggled for a long time, and was following an example I saw elsewhere, which did handle the time calculation...but now I need to do the rounding. I wonder...should I not be able to somehow use a new field to store this is a NUMBER, then use a round function to round up to the nearest 60?

Re: Function to round time UP to nearest hour?

Hilary Stoupa — Wed, 09 Sep 2015 22:00:25 GMT

Sorry, I'd help but most of those complex time formulas are beyond me.... is ElapsedTime being set by a rule or by a default value? I guess I'm wondering if you can't check the string after the ":" to see if it is anything other than "00" and then do something to increment the string before the ":"?

Re: Function to round time UP to nearest hour?

Sailor — Mon, 07 Sep 2015 04:26:07 GMT

I'm hoping someone will come to my rescue!

Function to round time UP to nearest hour?

Sailor — Fri, 04 Sep 2015 14:28:52 GMT

Hello, Due to InfoPath not being able to add/subtract time out of the box, I spent multiple hours figuring out how to subtract start and end time. So now I have a result formatted as string, but I want to ROUND UP to the nearest hour. So for 00:25 I want it rounded to 01:00, and 01:40 to 02:00, etc. Then I need the sum of this result (they are in a repeating table). With the results formatted as a string, I don't imagine I can using the Ceiling function...

Here is what I've done to create the Elapsed_Time

concat(substring(concat(substring-before(../my:Total_Minutes div 60, "."), ":", ../my:Total_Minutes mod 60), 1, contains(../my:Total_Minutes div 60, ".") * string-length(concat(substring-before(../my:Total_Minutes div 60, "."), ":", ../my:Total_Minutes mod 60))), substring(concat(../my:Total_Minutes div 60, ":", ../my:Total_Minutes mod 60), 1, not(contains(../my:Total_Minutes div 60, ".")) * string-length(concat(../my:Total_Minutes div 60, ":", ../my:Total_Minutes mod 60))))

Using the Total_Minutes field

substring(((substring-before(../my:End_Time, ":") * 60) + substring-before(substring-after(../my:End_Time, ":"), ":")) - ((substring-before(../my:Start_Time, ":") * 60) + substring-before(substring-after(../my:Start_Time, ":"), ":")), 1, (../my:Start_Time != "" and ../my:End_Time != "") * string-length(((substring-before(../my:End_Time, ":") * 60) + substring-before(substring-after(../my:End_Time, ":"), ":")) - ((substring-before(../my:Start_Time, ":") * 60) + substring-before(substring-after(../my:Start_Time, ":"), ":"))))