4 Replies Latest reply on Jan 26, 2009 6:32 PM by adcoleman

    Need calculation to combine a starting date with an ending date

    adcoleman

      Title

      Need calculation to combine a starting date with an ending date

      Post

      Presently I have a standard date field for entering the starting date of an event (such as a seminar). I also have a date field to indicate its ending date.

       

      In the same layout, I have a checkbox field named Date Type, enabling me to indicate how I want to display the event start date in a fourth field, named Display Date. My value list in Date Type lets me pick Day, Month, or year. If I pick Day, I get the day, month, and year; if I pick Month, I get just month and year; and if I pick year, I get just the year.

       

      This is achieved successfully via the following calculation:

       

      Let( m = If ( Filter ( Month ( Start_Date ) ; 567 ) ; MonthName ( Start_Date ) & " " ; Left ( MonthName ( Start_Date ) ; 3 ) & ". " ) ; Case( Date Type = "Day" ; Day ( Start_Date) & " " & m & Year ( Start_Date) ; Date Type = "Month" ; m & Year ( Start_Date ) ; Date Type = "Year" ; Year ( Start_Date) ) )

       

      I want to enhance this calculation so that it will combine the start date and the end date (with a hyphen in between), in the same configurations -- e.g., starting day month year-ending day month year, starting month year-ending month year, starting year-ending year.

       

      However, if the event starts and ends on the same day I want the result field to show only the start date in the selected configuration.

        • 1. Re: Need calculation to combine a starting date with an ending date
          raybaudi
             Hi try this one:

          Let([
          mStart = If ( Filter ( Month ( Start_Date ) ; 567 ) ; MonthName ( Start_Date ) & " " ; Left ( MonthName ( Start_Date ) ; 3 ) & ". " ) ;
          mEnd = If ( Filter ( Month ( End_Date ) ; 567 ) ; MonthName ( End_Date ) & " " ; Left ( MonthName ( End_Date ) ; 3 ) & ". " )
          ];
          Case(
          End_Date > Start_Date ;
          Case(
          Date Type = "Day" ; Day ( Start_Date ) & " " & mStart & Year ( Start_Date ) & " - " & Day ( End_Date ) & " " & mEnd & Year ( End_Date );
          Date Type = "Month" ; mStart & Year ( Start_Date ) & If ( mStart & Year ( Start_Date ) ≠ mEnd & Year ( End_Date ) ; " - " & mEnd & Year ( End_Date ) );
          Date Type = "Year" ; Year ( Start_Date ) & If ( Year ( Start_Date ) ≠ Year ( End_Date ) ; " - " & Year ( End_Date ) )
          );
          End_Date = Start_Date or IsEmpty ( End_Date );
          Case(
          Date Type = "Day" ; Day ( Start_Date) & " " & mStart & Year ( Start_Date ) ;
          Date Type = "Month" ; mStart & Year ( Start_Date ) ;
          Date Type = "Year" ; Year ( Start_Date )
          )
          )
          )









          • 2. Re: Need calculation to combine a starting date with an ending date
            adcoleman
              

            Thanks, raybaudi. Now, one last tweak . . .

             

            For an event that's ongoing, such as membership in a professional organization, standard practice is to indicate this as follows: Mar. 2008

             

            So if in a record I enter a Start Date but leave the End Date field empty, I want the Display Date field to show me the Start Date followed by a dash.

             

            I think I see where in your calculation this would go, but I don't know how to parse it . . . 

            <!--  StartFragment  -->
            • 3. Re: Need calculation to combine a starting date with an ending date
              raybaudi
                 Let([
              mStart = Case ( Filter ( Month ( Start_date ) ; 567 ) ; MonthName ( Start_date ) & " " ;  not IsEmpty ( Start_date ) ; Left ( MonthName ( Start_date ) ; 3 ) & ". " ) ;
              mEnd = Case ( Filter ( Month ( End_date ) ; 567 ) ; MonthName ( End_date ) & " " ; not IsEmpty ( End_date ) ; Left ( MonthName ( End_date ) ; 3 ) & ". " )
              ];
              Case(
              End_date > Start_date or IsEmpty ( End_date ) ;
              Case(
              Date Type = "Day" ; Day ( Start_date ) & " " & mStart & Year ( Start_date ) & If ( Start_date  ≠ End_date ; "—" & Day ( End_date ) & " " & mEnd & Year ( End_date ) );
              Date Type = "Month" ; mStart & Year ( Start_date ) & If ( mStart & Year ( Start_date ) ≠ mEnd & Year ( End_date ) ; "—" & mEnd & Year ( End_date ) );
              Date Type = "Year" ; Year ( Start_date ) & If ( Year ( Start_date ) ≠ Year ( End_date ) ; "—" & Year ( End_date ) )
              );
              End_date = Start_date ;
              Case(
              Date Type = "Day" ; Day ( Start_date) & " " & mStart & Year ( Start_date ) ;
              Date Type = "Month" ; mStart & Year ( Start_date ) ;
              Date Type = "Year" ; Year ( Start_date )
              )
              )
              )








              • 4. Re: Need calculation to combine a starting date with an ending date
                adcoleman
                  

                Perfecto, Daniele. Molto grazie! I made a slight revision so that when joining two dates (as in Start Date and End Date) I get a hyphen (-), yielding a result such as Mar. 2004-June 2005, but when indicating an open/ongoing event I get an em dash (—), with a result like 2007—. Here's the final version:

                 

                Let([

                mStart = Case ( Filter ( Month ( Start_Date ) ; 567 ) ; MonthName ( Start_Date ) & " " ;  not IsEmpty ( Start_Date ) ; Left ( MonthName ( Start_Date ) ; 3 ) & ". " ) ;

                mEnd = Case ( Filter ( Month ( End_Date ) ; 567 ) ; MonthName ( End_Date ) & " " ; not IsEmpty ( End_Date ) ; Left ( MonthName ( End_Date ) ; 3 ) & ". " )

                ];

                Case(

                End_Date > Start_Date ;

                Case(

                Date Type = "Day" ; Day ( Start_Date ) & " " & mStart & Year ( Start_Date ) & If ( Start_Date  ≠ End_Date ; "-" & Day ( End_Date ) & " " & mEnd & Year ( End_Date ) );

                Date Type = "Month" ; mStart & Year ( Start_Date ) & If ( mStart & Year ( Start_Date ) ≠ mEnd & Year ( End_Date ) ; "-" & mEnd & Year ( End_Date ) );

                Date Type = "Year" ; Year ( Start_Date ) & If ( Year ( Start_Date ) ≠ Year ( End_Date ) ; "-" & Year ( End_Date ) )

                );

                Case(

                IsEmpty ( End_Date ) ;

                Case(

                Date Type = "Day" ; Day ( Start_Date ) & " " & mStart & Year ( Start_Date ) & If ( Start_Date  ≠ End_Date ; "—" & Day ( End_Date ) & " " & mEnd & Year ( End_Date ) );

                Date Type = "Month" ; mStart & Year ( Start_Date ) & If ( mStart & Year ( Start_Date ) ≠ mEnd & Year ( End_Date ) ; "—" & mEnd & Year ( End_Date ) );

                Date Type = "Year" ; Year ( Start_Date ) & If ( Year ( Start_Date ) ≠ Year ( End_Date ) ; "—" & Year ( End_Date ) )

                );

                End_Date = Start_Date ;

                Case(

                Date Type = "Day" ; Day ( Start_Date) & " " & mStart & Year ( Start_Date ) ;

                Date Type = "Month" ; mStart & Year ( Start_Date ) ;

                Date Type = "Year" ; Year ( Start_Date )

                )

                )

                )

                ) 

                 

                Best wishes,

                Allan

                 

                (My Italian is so pathetic that the waiters joke about it loudly in the kitchen of my neighborhood trattoria.)