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

# Need calculation to combine a starting date with an ending date

### 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
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

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

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