• English
• Account
• API
• Integrations
• Enterprise
• Resources
Working with date functions
• 11 Sep 2023
• Dark
Light

# Working with date functions

• Dark
Light

Article Summary

In this article, we’ll take a look at functions that will help you work with dates and times (“datetimes”). As you learn about these functions, feel free to try them out to practice in a formula field in one of your tables that contains a date and time field.

First things first, date functions can be split up into three primary categories:

1. Returning specific datetimes
2. Formatting strings
3. Returning dates

In the following sections, we’ll go over functions that belong in each of those categories. If you’d like to skip to a specific section, just click on its name in the last above.

## Returning specific datetimes

These functions return a specified value according to the referenced datetime. For example, if you wanted to return the month of every completion date in a table, you would write: `MONTH({Completion date}).`

Here are the functions that work in this way:

• `YEAR()`
• `MONTH()`
• `DAY()`
• `HOUR()`
• `MINUTE()`
• `SECOND()`

To practice, enter a few dates into a table, and use a few of these functions to output a different result. Since they all work similarly you don't necessarily need to practice each one - but knowing how the work categorically is key.

## Formatting strings

The functions below format datetimes into specific strings - either as a time, or as a date.

• `TIMESTR()`
• `DATESTR()`

For example, if you wanted to format the current date and time provided by `NOW()` only as the time, you would use this formula:

``````TIMESTR(NOW ())
``````

Using the dates you listed in the previous section, practice using these two functions to format the dates in different ways.

## Returning dates

These last date and time functions can be used to return the day or week of the year according to whatever datetime they reference.

• `WEEKDAY(): return the day of the week`
• `WEEKNUM(): return the week of the year`

For example, if you want to organize a table by week of the year, you would use a formula like this:

``````WEEKNUM  ({Date})
``````

Practice using these functions on the dates in your tables.

## Workday

This function doesn't quite fit with the other categories, and works similarly the `DATEADD()` function covered previously.

You can use WORKDAY() to return the date after a specific number of working days. For example, if you wanted to always calculate a 30 working days after a start date, you would use a formula like this:

``````WORKDAY  ({Start Date},  30  )
``````

Working days exclude weekends and an optional list of holidays, formatted as a comma-separated string of ISO-formatted dates (similar to `WORKDAY_DIFF()`).

## Practice

Want to practice working with datetimes? Setup a base that leverages an IF statement along with this function to output different dates. Follow these steps:

1. Create a random list of 10 dates in a {Start Date} field
2. Create a single select field with values of Type 1 and Type 2
3. For 5 records, assign Type 1
4. For 5 records, assign Type 2

Next, create a formula to output a different number of working days depending on the type:

• For Type 1 records, add 7 days
• For Type 2 records, add 14 days

What formula did you come up with? If you’re having trouble, or simply want to check your work, you can click the box below to see a correct formula.
WORKDAY() Formula

```````IF({Type}="Type 1", WORKDAY({Start Date}, 7), IF({Type}="Type 2", WORKDAY({Start Date}, 14), {Start Date}))`
``````

Of course, this formula can be written in more ways than one so yours may not precisely match the example. If your formula’s a little different, or even used a different conditional function, it’s still correct if it gets the job done all the same.