1 2 3 Previous Next 30 Replies Latest reply on May 18, 2016 7:38 PM by darrynpeterlowe

    How do I round a time stamp minute?

    darrynpeterlowe

      I've got most of the essentials for a project I'm building but I'm having an issue with rounding timestamps.

       

      I have a button that when pressed enters into the field the current timestamp. But it inevitably ends up with something like:

       

      16/05/2016 10:02:07

       

      The tricky thing is that I want the minute to be either a 0 or a 5 but I'm not sure how I would do that.

       

      Typical rounding would be to round it to 10:00 but it gets a little tricky for other fields so I'll describe this in detail a little bit more now.

       

      So I have the following fields:

       

      Start Travel

      Onsite

      Offsite

      End Travel

       

      Now if Start Travel was 10:02 then I'd want it to be rounded to 10:00 but if it took me 1 minute to get to site which in this case would make Onsite 10:03 then I don't want it to round down to 10:00 otherwise the time would be 0 minutes so I would want it to round UP to make the time 10:05.

       

      Obviously there would need to be some checking of the previous field in order to round up (or maybe not you'd just round the time up regardless I guess) but in any case I don;t know how I'd do it. Does it need to be converted to numbers then the result displayed as timestamp or what?

       

      Thanks for your help.

        • 1. Re: How do I round a time stamp minute?
          erolst

          Try this

           

          Let ( [

            ival = 300 ;

            ts = Get ( CurrentTimeStamp ) ;

            sec = Mod ( Get ( CurrentTime ) ; ival ) ;

            rounded =

              Case (

                sec < ival / 2 - 1 ;

                Floor ( ts / ival  ) * ival ;

                Ceiling ( ts / ival ) * ival

               ) // yes, there is some redundancy …

            ] ;

            GetAsTimeStamp ( rounded )

          )

           

          I wouldn't bet against there being an easier solution, though …

          • 2. Re: How do I round a time stamp minute?
            user19752

            Do you want 10:07 to 10:05 and 10:08 to 10:10 liike usual  "round to five" ?

            • 3. Re: How do I round a time stamp minute?
              darrynpeterlowe

              Yes. I want there to be a minimum of 5 minutes even if there is only 1 minute between me pressing the button to trigger Start Travel and pressing the button to trigger Onsite.

               

              @erolst, code does seem to work but it does have some odd results every now and again which can end up with 10:03 being rounded up to 10:05 which means that one minute from that would show 10:05 because 10:04 also got rounded up.

              • 4. Re: How do I round a time stamp minute?
                user19752

                I'm not sure, do you want

                1) enter timestamp in Start Travel

                2) enter timestamp in Onsite

                3) If Onsite - Start Travel >= 1min, round Onsite other than Start Travel to 5min, then where set the rounded timestamp?

                • 5. Re: How do I round a time stamp minute?
                  darrynpeterlowe

                  Right, I'll try to explain this as best as I can.

                   

                  I have the following fields:

                   

                  Start Travel

                  Onsite

                  Offsite

                  End Travel

                   

                  I do not want ANY of these fields to end in anything other than a 0 or a 5. So I would want say 10:01 - 10:04 to round down to 10:00. 10:05 can stay the same but I would like 10:06 - 10:09 to round up to 10:10.

                   

                  However, this rounding would also be applied to the next field but if I started travelling at 10:03 and got to site at 10:04 then what currently happens is the rounding happens exactly the same to both fields for Start Travel is rounded to 10:00 and Onsite is also rounded to 10:00 giving a 0 time to site. So I would still want 10:04 to be rounded for Onsite but it would need to be rounded to 10:05 not 10:00.

                   

                  What I'm thinking is that I should follow the same rounding at Start Travel but +5 so that it would force a five minute gap. Does this sound like a reasonable approach?

                  • 6. Re: How do I round a time stamp minute?
                    user19752

                    Ok, you want auto-enter calculation from Self value.

                     

                    Does this "gap" rule applied to only Start between Onsite?

                    • 7. Re: How do I round a time stamp minute?
                      electon

                      darrynpeterlowe wrote:

                       

                      Right, I'll try to explain this as best as I can.

                       

                      I have the following fields:

                       

                      Start Travel

                      Onsite

                      Offsite

                      End Travel

                       

                      I do not want ANY of these fields to end in anything other than a 0 or a 5. So I would want say 10:01 - 10:04 to round down to 10:00. 10:05 can stay the same but I would like 10:06 - 10:09 to round up to 10:10.

                       

                      However, this rounding would also be applied to the next field but if I started travelling at 10:03 and got to site at 10:04 then what currently happens is the rounding happens exactly the same to both fields for Start Travel is rounded to 10:00 and Onsite is also rounded to 10:00 giving a 0 time to site. So I would still want 10:04 to be rounded for Onsite but it would need to be rounded to 10:05 not 10:00.

                       

                      What I'm thinking is that I should follow the same rounding at Start Travel but +5 so that it would force a five minute gap. Does this sound like a reasonable approach?

                      Itt looks to me a bit circular between those two time fields. Whenever you modify the first one it influences the second one.  Wouldn't it be easier to leave those fields alone and just perform the rounding on a third "duration" field?

                      in a simpler manner to perform the calculations only on the second digit of the minutes portion of the duration:

                       

                      Case

                      dur < 5 ; 5 ;

                      dur < 10 ; 10 ;

                      dur

                      • 8. Re: How do I round a time stamp minute?
                        user19752

                        Rounding rule is changed from first post, and if

                        Start 10:06 is rounded to 10:10, then

                        Onsite 10:07 is rounded to 10:15 ??

                        I feel adding 8 minitues make it meaningless...

                         

                        It should be each value have same width that

                        09:58...10:02 to 10:00

                        10:03...10:07 to 10:05

                        10:08...10:12 to 10:10

                        but result is not so different, Start 10:08 rounded to 10:10 then Onsite 10:09 become 10:15.

                         

                        First of all, why do you need to round the time?

                        • 9. Re: How do I round a time stamp minute?
                          electon

                          I'm sorry, the previous post was done on a tablet. The calculation is also not complete.

                           

                          What I meant to say and it's probably totally inappropriate to what you're trying to do, is this:

                          I'd make the rules outside of time registering. Leave the start and onsite as is, calculate the duration separately and apply the rules there.

                          My reasoning behind is that:

                          1) you actually register data that does not reflect reality.

                          2) it will influence the results down the line, especially if you use a button to time the process

                          It can be more difficult to track this down the line.

                          If you add 2 minutes to travel time you will have to remember to account for this in the On Site time.

                          Otherwise when you're Off Site you may have billable time less two minutes.

                          It gets complicated with 4 time fields. That's why I'd make the rules outside of time registering and make the rules clear "We bill travel time in 5 minute increments". This way you can easily change the rules if you need to without messing with the input data.

                           

                          So this is one example how to calculate it within the 0 to 9 minute range:

                           

                           

                          Let ([

                            startTravel = Time ( 00 ; 00 ; 00 );

                            onSite = Time ( 00 ; 01 ; 00 );

                           

                           

                            travelTime = GetAsText ( onSite - startTravel );

                           

                            mL = Middle ( travelTime ; 4 ; 1 );

                            mR = Middle ( travelTime ; 5 ; 1 );

                           

                           

                            minutes = Case (

                            mR <= 5 ; GetAsNumber ( mL & 5 ) ;

                            mR <= 9 ; GetAsNumber ( mL + 1 & 0 )

                            )

                           

                            ];

                           

                            Time ( Hour ( travelTime ) ; minutes ; 00 )

                          )

                          • 10. Re: How do I round a time stamp minute?
                            beverly

                            You can add a MAX() function so that the time has a value rather than 0.

                             

                            Max( Onsite - StartTravel ; GetAsTime("00:05:00") )

                             

                            If it is MORE than 5 minutes, it will be 10, 15, etc. But if it is "0" or "5", it will be at least 5 minutes.

                             

                            beverly

                            • 11. Re: How do I round a time stamp minute?
                              erolst

                              darrynpeterlowe wrote:

                              @erolst, code does seem to work but it does have some odd results every now and again which can end up with 10:03 being rounded up to 10:05 which means that one minute from that would show 10:05 because 10:04 also got rounded up.

                              That's not really odd, it's in the code; everything up to and including 02:29 / 07:29 … (300 / 2 - 1 in seconds) is rounded down, everything else is rounded up.

                               

                              You could just change the ranges that are considered in the conditional.

                               

                              i.e. Case ( sec < 240 ; Floor ; Ceiling ) would round down the first 4 minutes in every block of 5, and round up only the last one.

                               

                              Of course, you didn't mention originally that you need to cater for two interlocked fields … in that case the second value could be made dependent on its own value and the one that was set as first – e.g.

                               

                              Max ( calculation ; firstField + 300 )

                              • 12. Re: How do I round a time stamp minute?
                                Paul Jansen

                                I would take a slightly different approach; I would never change the actual times of the events as discarding the original data seems like a bad idea to me.

                                 

                                When I had a similar scenario, I made the adjustments when calculating the travel and onsite durations.

                                 

                                So (pseudocode)

                                 

                                travel time = max( round( onsite - startTravel ) ; 5 ) ; 5 )

                                OnSite time = max( round( OffSite - OnSite ; 5 ) ; 5 )

                                travel time 2 = max( round( endTravel - offSite ) ; 5 ) ; 5 )

                                 

                                This approach enables transparency over how the hours to be billed/paid have been calculated and keeps the original data for review in case any queries are raised.

                                 

                                I almost forgot to say.  I would calculate the durations in a script, as my experience is that the rules and boundary conditions change and a scripted process gives you control over which records get updated when the rules change.  I also found the scripted process much easier to debug!

                                 

                                PJ

                                • 13. Re: How do I round a time stamp minute?
                                  beverly

                                  I always try to preserve the "raw" data, as well, Paul. The 'rounding' can be used as needed, but audits like really values sometimes.

                                  beverly

                                  • 14. Re: How do I round a time stamp minute?
                                    electon

                                    I couldn't agree more!

                                    On the scripted method as well.

                                     

                                    It would make sense to round off from scheduling perspective but not from actual time keeping.

                                    Unless I possessed time shifting powers and somehow arrive 2 minutes after I actually did.

                                    LOL

                                    1 2 3 Previous Next