Mastering Date and Time Functions in Notion Formulas!

Notion Mar 14, 2023

In the world of productivity and organization, keeping track of deadlines and appointments is crucial. Notion is a powerful tool that can help you do just that, and with its built-in formulas, you can automate many tasks related to date and time management. In the previous article, we talked about Logic Functions in Notion formulas. 👀

In this article, we'll explore the various date and time functions that you can use in Notion formulas, and show you how to use these functions for building formulas in your workspace! 🚀

Following are the 15 Date and Time functions that can be used to build formulas in Notion:

date
dateAdd
dateSubtract
dateBetween
formatDate
fromTimestamp
end
now
day
hour
minute
month
start
timestamp
year

Today we will learn how to use the first 8 of the Date and Time functions mentioned above. Just like the previous articles in this series, In this article we will take an example database to make it easier to understand. ✅

1. date()

The 'date' function allows you to extract the date component of a 'date' and time value. The syntax of the date function is

date(date_time_value)

For example, if you have a date and time value of "2022-01-01 12:00:00" and you want to extract the date component, using date("2022-01-01 12:00:00") would return "2022-01-01". You can see below that using the date() function with the 'created time' property gives us the date. 🔽

Notion Formulas | Date and Time Functions — *date() statement result*

2. dateAdd()

The 'dateAdd' function allows you to add a certain number of days, months, or years to a date. The syntax of the 'dateAdd' function is

dateAdd(date_value, number, "seconds/minutes/hours/days/weeks/months/years etc.")

For example, if you have a date value of "2022-01-01" and you want to add 3 days, you would use the formula dateAdd("2022-01-01", 3, "days"), which would return "2022-01-04".

Here I have used the 'dateAdd' function to build a formula that adds 5 days to the date of creation. 🔽

dateAdd(prop("Created time"), 5, "days")

dateAdd() statement example

3. dateSubtract()

The 'dateSubtract' function allows you to subtract a certain number of days, months, or years from a date. The syntax of the 'dateSubtract' function is dateSubtract(date_value, number, "seconds/minutes/hours/days/weeks/months/years etc.")

For example, if you have a date value of "2022-01-01" and you want to subtract 3 days, you would use the formula dateSubtract("2022-01-01", 3, "days") which would return "2021-12-29".

As you can see, I have used a dateSubtract function to build a simple formula that displays a date that is 3 days before the date of creation. 🔽

dateSubtract(prop("Created time"), 3, "days")

dateSubtract() statement example

4. dateBetween()

The 'dateBetween' function allows you to check if a date falls within a certain range. The syntax of the 'dateBetween' function is

dateBetween(start_date, end_date, "hours/days/months etc.").

For example, if you have a date value of "2022-01-01" and you want to check if it falls within the range of "2021-12-01" to "2022-01-15", you would use the formula dateBetween("2021-12-01", "2022-01-15", "hours/days/months etc."), which would return "true".

As you can see, I have used the dateBetween function formula that shows the number of days between the Due date and the date of creation. 🔽

dateBetween(prop("Due Date"), prop("Created time"), "days")

dateBetween() statement example

5. formatDate()

The 'formatDate' function allows you to format a date according to a specified pattern. The syntax of the 'formatDate' function is

formatDate(date_value, pattern)

Note that you can use different combinating of uppercase and lowercase letters to change the format of the date. For example, if you have a date value of "2022-01-01" and you want to format it as "01-Jan-2022", you would use the formula formatDate("2022-01-01", "dd-MMM-Y").

As you can see, I built a formula using the 'formatDate()' function that shows the date in the format that I have specified using combination of lowercase and uppercase letters. (D, M, Y) 🔽

formatDate(prop("Created time"), "ddd, D MMM, Y")

formatDate() statement example

6. fromTimestamp()

The 'fromTimestamp' function allows you to convert a timestamp (a number of seconds since the Unix epoch) to a date and time value. The syntax of the 'fromTimestamp' function is

fromTimestamp(timestamp)

For example, if you have a timestamp of 1609459200 and you want to convert it to a date and time value, you would use the formula fromTimestamp(1609459200), which would return "2022-01-01 00:00:00".

I build a formula using the 'fromTimestamp' and 'formatDate' functions that shows the date based on the number you input in the "Unix Timestamp' property. 🔽

formatDate(fromTimestamp(prop("Unix Timestamp")), "ddd, D MMM, Y")

fromTimestamp() statement example

7. now()

The 'now' function returns the current date and time. The syntax of the 'now' function is now(). For example, you can use the formula now() to get the current date and time value.

Example:

8. end()

The 'end' function allows you to extract the end of a date and time value, depending on the specified granularity. The syntax of the 'end' function is

end(date_time_value, granularity)

For example, if you have a date and time value of "2022-01-01 12:00:00" and you want to extract the end of the day, you would use the formula end("2022-01-01 12:00:00", "day"), which would return "2022-01-01 23:59:59".

Below you can see that if we apply the 'end()' function on the 'Date' property, it gives us the end date only. 🔽

end(prop("Date"))

end() function example

Now you can see, I have created a formula that displays the "Urgent" warning if the end date of the task is today! 🔽

if(formatDate(end(prop("Date")), "D MM") == formatDate(now(), "D MM"), "Urgent", "")

end() statement example

Conclusion

We have successfully covered a little over half of the Date & Time functions used in Notion formulas! For practice, you can try to follow what I've done here and try to customize it to your own liking. I hope this article was easy to understand and it helped you understand how to build your own custom formulas using these functions. You can also have a look 👀 at our shortcut commands template to be more efficient using these formulas🤩

Remember to check out the whole Fantastic Formulas series for a complete and comprehensive guide on Notion formulas! ⚡

Follow us on 🐦 Twitter for daily updates, awesome deals on Notion templates and helpful tips to get the most out of your Notion workspace! If you are a Pro Notion user and have some tips that you would like to share with other users, you can write for us! Reach out to us via email at help@prototion.com or DM us on Twitter! 📧