10 Replies Latest reply on May 9, 2013 5:43 AM by Alasdair

    Timestamps

    Alasdair

      Hi

       

      I'm looking for a calculation formula to give me a result in another field from an ''If function'' for BETWEEN two dates or timestamps?

       

      This formula give me a correct result for after a date

       

      C_BerthFree = If ( DateTimeOut ≤ LastModificationTime ; "Free" ; "" )

       

      Can anyone help with this?

       

      Thanks

        • 1. Re: Timestamps
          Mike_Mitchell

          How about:

           

               If ( DateTimeOut <= LastModificationTime and DateTimeOut >= CreateTime ; "I'm Between" ; "I'm Not Between" )

           

          The key is the "and" clause.

           

          HTH

           

          Mike

          • 2. Re: Timestamps
            Alasdair

            Hi Mike

             

            Thanks very much for your reply,

             

            I have tried this however it isn't working? (No matter what timestamp ranges are entered, I'm getting the same result "I'm not Between")

             

            I have used a Get (CurrentTime) calculation field in place of your CreateTime

             

            I may have this bit wrong, how are you getting CreateTime?

             

            Regards

             

            Alasdair

            • 3. Re: Timestamps
              Mike_Mitchell

              It's just an example.

               

              If you want the current time, you'll need to set the calculation to be unstored (choose Storage Options > Indexing > Do not store calculation results -- recalculate when needed). Otherwise, the calculation will record the "current" time as being the time when you closed the Manage Database dialog.

              • 4. Re: Timestamps
                LyndsayHowarth

                You of course need the fields to the Timestamp fields... (yes... obvious but sometimes the obvious is the culprit)

                 

                - Lyndsay

                • 5. Re: Timestamps
                  Alasdair

                  Hi Folks

                   

                  Thanks very much for your input much appreciated.

                   

                  I'm a newby here and have failed to achieve the correct result.

                   

                  In my layout I’m trying to achieve a “FULL” or “EMPTY” text result.

                   

                  Ie, if the current time is between my two TimeStampes the result is “Full” and if the current time is before or after my two TimeStamps then the result is “Empty”.

                   

                  I’m most likely doing something stupid, so attached is my file. Please be kind enough to put me out of my misery!

                   

                  Regards to all

                   

                   

                  Alasdair

                  • 6. Re: Timestamps
                    Mike_Mitchell

                    Okay, there are three problems:

                     

                    1) First, my bad; I had the calculation inverted for placing the value between two other values. Should have been:

                     

                         If ( DateTimeOut >= LastModificationTime and DateTimeOut <= C_GetCurrentTimestamp ; "Full" ; "Empty" )

                     

                    (Note that your value is greater than or equal to one and less than or equal to the other.)

                     

                    2) You're comparing timestamps, not dates or times. All values have to be the same type, so I added a timestamp value (C_GetCurrentTimestamp).

                     

                    3) That said, though, I'm confused about exactly what you're trying to achieve with this calculation. DateTimeOut is a standard timestamp field. When the user updates it, it will always be equal to or slightly less than (more likely) the modification timestamp. It's unlikely this test will ever come up true. Perhaps if you just tell us, in standard English, what you're trying to achieve, we can help more.

                     

                    Anyway, corrected file (as far as it goes) attached.

                     

                    Mike

                    • 7. Re: Timestamps
                      Alasdair

                      Hi Mike

                       

                      Thanks

                       

                      I think I have confused things so here goes.

                       

                      In my Marina we use "Berths" which are Parking spaces for yachts. so each of my records represents a Berth A1, A2, etc.

                       

                      When we enter a TimeStamp in DateTimeIn (Yacht arriving) and a TimeStamp DateTimeOut (Yacht departing) we want to show the status of that Berth

                      Full or Empty, the dates we enter could be an antisipated arrival of a yacht, so therefore we will still return empty until the due arrival time when the return will change to Full. This will allow us to control the comings and goings of yachts.

                       

                      In the file you kindly sent back I only got returns of Empty.

                       

                      Regards

                       

                       

                      Alasdair

                      • 8. Re: Timestamps
                        Mike_Mitchell

                        If all you care about is if the exit date/time is earlier than the current date/time, why do you care about the modification date/time? You should be able to do it with just this:

                         

                             If ( DateTimeOut <= C_GetCurrentTimestamp and not IsEmpty ( DateTimeIn ) ; "Full" ; "Empty" )

                         

                        Mike

                        • 9. Re: Timestamps
                          Mike_Mitchell

                          Changed my mind.       

                           

                          If you want it to show "Empty" until the arrival time (not the departure time), try this:

                           

                          1) Validate DateTimeOut to be greater than or equal to DateTimeIn.

                           

                          2) Change your "Berth Full" calculation to this:

                           

                          Case ( DateTimeIn >= C_GetCurrentTimestamp ; "Empty" ; not IsEmpty ( DateTimeIn ) and IsEmpty ( DateTimeOut ) ; "Full" ; DateTimeOut >= C_GetCurrentTimestamp ; "Full" ; "Empty" )

                           

                          (As you can see, it's a bit more complicated.)         

                           

                          Revised version attached.

                           

                          Mike

                          • 10. Re: Timestamps
                            Alasdair

                            Hi Mike

                             

                            I can't thank you enough for your help!

                             

                            It's working as required, ironically I tried a case at one time, couldn’t get that right either...

                             

                            Once again many thanks

                             

                            Alasdair