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

Learn how to extract start and end dates from a date/time field with this article's step-by-step walkthrough.

This article covers the following topics:

• Use Case

• Requirements

• Steps

  • 1.  Add a Text Formula Field to Extract the Start Date

  • 2.  Add a Text Formula Field to Extract the End Date

  • 3.  Add a Date/Time Field for the Start Date

  • 4.  Add a Date/Time Field for the End Date

  • 5.  Your Results

Use Case

Utilizing the advanced calendar features of a date/time field to capture both the start and end dates, you can then display this information on a calendar. To further use these dates in calculations, it is necessary to extract them from the original date/time field while still retaining the ability to display them on the calendar.

This can be achieved by extracting the start and end dates using text formulas and then implementing conditional rules on new date/time fields to convert the extracted values back into dates for calculation purposes.

Requirements

We’ll also be using text formulas to extract the start and end dates and will be applying a conditional rule to a field. Check out the following articles for information on each:

• About Text Formulas

• Using Conditional Rules

Steps

1.  Add a Text Formula Field to Extract the Start Date

Assuming that you already have a date/time field created in a table that has calendar options enabled, let's start by creating a text formula field named "Extract Start Date". This text formula field will extract the start date from your date/time field.

In the following example below, the original date/time field with calendar options enabled is named "Date".

In the equation editor, the following formula will be needed in the "Extract Start Date" text formula field: left({Date},10)

2.  Add a Text Formula Field to Extract the End Date

Next, you can create another text formula field named “Extract End Date”. This text formula field will extract the end date from your original date/time field.

There are three different ways in which your text formula field may need to be set up:

• 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 text formulas to extract the dates:

  • "Extract Start Date" field: left({Date},17)

  • "Extract End Date" field: left({Date},10) mid({Date},20,10) 

3.  Add a Date/Time Field for the Start Date

Now, you can put the values from the two text formulas into date/time fields, so that they can be used for date equations.

• Create a date/time field named “Start Date”.

• Add the following conditional rule to the field: 

4.  Add a Date/Time Field for the End Date

Next, you can create a date/time field named "End Date", and add the following conditional rule to the field: 

5.  Your Results

Now, you have four new fields added to your table, but you'll only be using the two date/time fields you created following steps 3 and 4 for your date equations. 🚀

Fields in the Builder:

Record View in the Builder:

In the Live App with these fields included in a grid view:

You will now be able to calculate the difference between the Start Date and the End Date in a date equation field: