How to check a date's day of the week using formulas

To check what day of the week a specific date lands on, such as determining if a date falls on a Sunday, you can use the datetime_format function in combination with a simple condition.

In this article, we’ll cover a step-by-step guide on how to achieve this.

Use cases

Checking the day of the week within a database can be useful in a variety of scenarios.

For example, businesses might use it to schedule tasks or send automated reminders based on specific days, like ensuring emails go out on weekdays only or tracking sales trends over weekends. It can also help in organizing events or meetings by flagging weekends and holidays, so you can avoid scheduling conflicts.

Also, it’s great for managing employee shifts, ensuring certain actions take place on designated days, or even tracking patterns in customer behavior depending on the day of the week. This kind of functionality can streamline operations and improve decision-making.

Check if a date is a specific day of the week

Step 1: Understand the datetime_format function

The datetime_format function in Baserow allows you to format a date according to a specific pattern. This function is quite powerful and can be used to extract different parts of a date, such as the day, month, year, or even the day of the week.

Syntax:

datetime_format(date, text)

datetime format tz converts the date to text given a way of formatting the date in the specified timezone. Example: datetime_format_tz(field('date field'), 'YYYY-MM-DD HH24:MI', 'Europe/Amsterdam')

Step 2: Write the formula

To check if a date falls on a Sunday, use the following formula:

datetime_format(field('date field'), 'D') = '1'

Let’s break down this formula:

• field('date field'): This refers to the date field in your Baserow table. Replace 'date field' with the actual name of your date column.
• 'D': The key part of the format string for checking the day of the week is 'D'.
• = '1': This condition checks if the extracted day equals 1, meaning the date is a Sunday.

This extracts the day of the week as a number, where 1 corresponds to Sunday and Saturday is 7.

Step 3: Apply the formula

1. Go to the table where you want to perform the check.
2. Add a new formula field to your table.
3. Enter the formula above into the formula field.

In this case, the date field is named “Date,” the formula to check if the day is Monday would look like this:

datetime_format(field('Date'), 'D') = '2'

This returns a number representing the day of the week, where Monday is 2.

Step 4: Use the result

This formula will return true if the date is a Monday and false otherwise.

When you add this formula, any row where “Date” is a Monday will have a true value in this new formula field.

You can then use this boolean result to trigger further actions or conditions within Baserow.

Step 5: Check for a range of days

To extend the formula to check for a range of days like weekends (Saturday and Sunday), you can modify the condition to check if the day falls within that range.

Remember, the days of the week are represented as numbers (Sunday = 1, Monday = 2, …, Saturday = 7). So, to check for weekends, you’d want to check if the day is either 1 (Sunday) or 7 (Saturday).

This formula ensures that the date is evaluated as falling on a weekend:

or(datetime_format(field('Date'), 'D') = '1', datetime_format(field('Date'), 'D') = '7')

This checks if the date falls on either Sunday or Saturday, representing weekends.

or function returns the logical or of the first and second argument, so if either is true then the result is true, otherwise it is false: or(boolean, boolean).

Conclusion

Using the datetime_format function in Baserow provides a simple and efficient way to determine the day of the week for a given date. By applying the correct formula, you can easily check if a specific date falls on a Sunday, Monday, or any other day of the week.

This can be particularly useful when managing workflows or triggering specific actions based on dates. With this step-by-step guide, you now know how to implement this functionality in your Baserow tables, ensuring that your data management becomes more dynamic and insightful.