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





      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?



        • 1. Re: Timestamps

          How about:


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


          The key is the "and" clause.





          • 2. Re: Timestamps

            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?





            • 3. Re: Timestamps

              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

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


                - Lyndsay

                • 5. Re: Timestamps

                  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




                  • 6. Re: Timestamps

                    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.



                    • 7. Re: Timestamps

                      Hi Mike




                      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.






                      • 8. Re: Timestamps

                        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" )



                        • 9. Re: Timestamps

                          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.



                          • 10. Re: Timestamps

                            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