3 Replies Latest reply on Aug 11, 2014 8:15 AM by philmodjunk

    Assign a number to a date

    StaceyManderscheid

      Title

      Assign a number to a date

      Post


           I am trying to assign numbers to dates for a hotel count.... but my formula isn't working. I would have a calculation for each potential date a person would be in a hotel room (across a 2 week span).  The calculation would use the arrival and departure dates to assign a value of 1 or 0.

            

           So what i had was...

           Case(Hotel_Departure  > 10/14/2014 ≥ Hotel_Arrival; 1; 0)

            

           Assuming I arrive on the 13th and stay to the 15th, this should assign a value of 1, but its not.  what am i missing??? 

        • 1. Re: Assign a number to a date
          philmodjunk

               No need for case, and your syntax is in error.

               Hotel_Departure > Date ( 10 ; 14 ; 2014 ) And Hotel_Arrival < GetAsDate ( "10/14/2014" )

               Will return the value 1 if 10/14/2014 is in the specified range of dates from arrival to departure.

               Note that in a FileMaker calculation 10/14/2014 is interpreted as 10 divided by 14 divided by 2014 so one of the two functions that I've used is needed to get FileMaker to treat 10 / 14 / 2014 as a date.

               But using a constant for the date seems a very impractical calculation. A date field or Get ( CurrentDate ) would seem a better option.

          • 2. Re: Assign a number to a date
            StaceyManderscheid

                 Thanks! I'll test this out!  I don't want to use current date because I need this over a selected time frame...for each day. Not just a snapshot of one day (CurrentDate). Or do I misunderstand your suggestion?

            • 3. Re: Assign a number to a date
              philmodjunk

                   The point that I am making is that 10/14/2014 will always be the date that you are comparing to the dates in the arrival and departure date fields. That seems unlikely to be useful to you and constantly redefining the calculation to use a different date seems very impractical. Thus it would seem a better idea to replace that constant October 14th date with the date from a field or function.