Logic, Equations, & Formulas
      Back to home
      1. Knowledge Base
      2. How-To Guides
      3. Logic, Equations, & Formulas

      How to Extract the Start and End Dates from a Date/Time Field

      Use Case

      You’re using the advanced calendar options on a Date/Time field to capture both a start and end date. You’re displaying that field on a calendar.

      You will also want to use the start and end dates in calculations. You will need to extract them from your original Date/Time field without losing the ability to display that field on the calendar.

      You can do this by extracting the start and end date using text formulas. Then, you can use conditional rules on two new Date/Time fields to convert the extracted values back into dates that will be available for your calculations.

      Here’s what it would look like:

       

      Requirements

      If this is your first time creating an app, you'll need to know some basics about adding tables, fields, pages, and views. You can start by reading our Builder Basics section. 
      Other good resources can be found in our About Your Database and Working With Pages article in our knowledge base.

      We’ll also be using text formulas to extract the start and end dates. Check out this article for information on text formulas.

      Steps

      Add Text Formula to Extract Start Date

      In the Data section of the Builder, create a text formula field named "Extract Start Date". This field will extract the start date from your Date/Time field. In this example, the original Date/Time field is named "Date".

      Use the following formula:

      left({Date},10) 

      Add Text Formula to Extract End Date

      Create another text formula field named “Extract End Date”. This field will extract the end date from your Date/Time field.

      If your original Date/Time field does not include start and end times, then you can use the following formula to extract the end date:

      right({Date},10)

       

      If your original Date/Time field does include start and end times and the start and end dates are different, then you can use the following formula to extract the end date:

      mid({Date},21,10)

       

      If your original Date/Time field does include start and end times and the start and end dates are the same, then you can use the following formula to extract the dates:

      Start date: left({Date},17)

      End date: left({Date},10) mid({Date},20,10) 

      Add a Date/Time Field for the Start Date

      Now, you're going to put the values from the two text formulas into Date/Time fields, so they can be used for date equations.

      In the Data section of the Builder, create a Date/Time field named “Start Date.”

      Add the following conditional rule to the field:

      • When Every Record.

      • Value Set to a record value “Extract Start Date”

      Add Date/Time Field for End Date

      Create a Date/Time field named “End Date.”

      Add the following conditional rule to the field:

      • When Every Record.

      • Value Set to a record value “Extract End Date”

      Your Results

      Now, you have four new fields, but you'll only be using the two Date/Time fields for your equations.

      In the Builder:

       

      In the Live App:

       

      For example, you can now calculate the difference between the Start Date and the End Date: