Table of Contents

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

Sarto Jama Updated by Sarto Jama

Scenario

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

But you also want to use the start and end dates in calculations. You need to extract them from your original date field without losing the ability to display that field on a 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 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 your first time creating an app, you'll need to know some basics about adding objects, fields, pages, and views. You can start by reading our Builder Basics section

Other good resources can be found in our designing the database and building pages sections of the 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 Schema section of the Builder, create a text formula field called “Extract Start Date.” This field will extract the start date from your date field. In this example, the original date field is called “Date.”

Use the following formula:

left({Date},10)

Add Text Formula to Extract End Date

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

If your original date field doesn’t include start and end times, then use the following formula to extract the end date:

right({Date},10)

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

mid({Date},21,10)

If your original date field does include start and end times and the start and end dates are the same, then use the following formula to extract the dates:

Start date: left({Date},17)

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

Add Date Field for Start Date

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

In the Schema section of the Builder, create a date field called “Start Date.”

Add the following conditional rule to the field:

  • When Every Record.
  • Value Set to a record value “Extract Start Date”

Add Date Field for End Date

Create a date field called “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 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:

How did we do?

Show Upcoming Birthdays

How to Display Multiple Date Formats Using a Single Date Field

Contact