How to Use ClickUp FORMULA Custom Fields (Advanced Examples)

In this video, we’re going to talk about ClickUp’s Formula Custom Field feature, and how you can actually put it to use. We’ll go over how to effectively use Formulas in ClickUp — and if those ClickUp Formulas can be used like Excel.

We’ll also review some simple and advanced formula use cases that incorporate useful equations, such as the “IF” “DAYS” and “ROUND” function in ClickUp.

Copy of Formulas Used:

• field(“Time estimated in hours”) – field(“Time tracked in hours”)
• (ROUND((field(“Time estimated in hours”)-field(“Time tracked in hours”)),2))& ” hours”
• ROUNDUP(field(“Time tracked in hours”)*field(“Hourly Rate”),0)
• IF((DAYS(field(“Due date”),TODAY())=0),”Yes, today!”, “You’re good.”)
• DAYS(field(“Due date”),TODAY())&” days left”
• IF((DAYS(field(“Due date”),TODAY())>0),(DAYS(field(“Due date”),TODAY())),”No”)&” day(s) left”

⭐ Want to work with me to get your ClickUp in order? Go to processdriven.co

⭐ Want to try ClickUp for yourself? Use my affiliate link processdriven.co/clickup

Related Resources

➤ What are Custom Fields? | ClickUp Tutorial for Beginners

Do you love ProcessDriven? Get paid for it! 💰

Video Blog Post

What follows is an AI-generated transcript from this video. Please be mindful that this transcript may not be 100% accurate.

[00:00:00.210]
ClickUp now has a formula custom field, basically a column and list view that allows you to calculate values based on other standard or custom fields you have in your ClickUp account. But what exactly is this formula field good for?

[00:00:13.740]
Will it replace tools like Notion, Coda, Excel, Airtable?

[00:00:17.280]
And if it could replace these other tools, should it? If you’re asking those questions, you are watching the right video. Today we’re going to be talking through what exactly this formula field does. Some use cases and example formulas you might try out for yourself, as well as my final thoughts on just how effective this formula custom field is.

[00:00:33.090]
Now, before we get started, let me say my name is Layla on this channel every single week, twice a week, I’m releasing videos about ClickUp and processes and how you can work with the two hand in hand inside your ClickUp account. So if you’re interested in those topics, be sure to subscribe and hit the bell. So you’re actually notified when new videos come out. Now, let’s dig in with what a formula custom field is.

[00:00:51.990]
Here in ClickUp, we can add a custom field by going to the end of a particular view and clicking plus to add a column and selecting formula from the custom field dropdown. Once you create it, you’re going to need to give it a name. We’re going to call this one test, click on this x icon and select what you want your formula to do. So, for example, it may be time tracked and hourly rate and you want to multiply them, great. But there’s also this advanced editor option down here on the bottom left. And this is where we can start doing some more interesting things, which I’m going to show you here in these other examples.

[00:01:20.070]
When you’re in the advanced editing options for your formula. It’s going to look a lot more like Excel or AirTable. Unlike Excel, you could skip the equal sign when you’re writing this advanced area, but you are going to want to start off with an operator. Every operator, just like Excel, has certain attributes that you need to have inside it. So I’m actually going to flip over my screen and I’ll take you step by step through creating a formula custom field for a few simple and, a few advanced ClickUp scenarios.

[00:01:45.330]
So let’s first unpack this time remaining one, because I think this is one that teams use pretty often. So just to click into this, we can see quickly how it’s put together – time estimated minus time tracked. That’s pretty easy. And we click calculate and that’s how we get the result. If I were to add more time estimated, let’s just say this was 40 minutes. 40 minutes. There we go. And I type that in, you’ll see how it immediately changes our time remaining.

[00:02:08.040]
Now, this is great, but it’s certainly not ideal when we’re looking at this and we’re seeing 1.33. 1.33, what? Hours, days, years, months.

[00:02:15.240]
So let’s look at this other way to calculate time remaining. Here rather than staying in the basic editor, we went into the advanced side of things. And rather than just having something like time estimated, minus time tracked, we went advanced.

[00:02:28.890]
And we’re going to add in a label field to let us know that it’s number of hours between these. To do this, we need to add the and sign an ampersand and then give it our label. Now, if I calculate here, it’s actually not going to work as expected. And I’ll show you why. The first mistake is that ClickUp itself has a weird bug around adding these words. We actually need to put this in parentheses in order to have this ampersand work properly.

[00:02:52.390]
So I’m going to calculate all of a sudden now we’ve got it going, but we’ve got two more problems.

[00:02:56.950]
First of all, ours is right on top of our letters. Probably want a little space there. So let’s go back through here and let’s add a space.

[00:03:03.780]
But then we also want to make sure that this number isn’t ridiculously long. And that’s kind of what we ended up doing in this version of Time Estimated. So I’ll just edit this one so we can make it match this version here. But let’s delete everything and start from scratch. We’re going to go to round, I can just start typing or I can scroll for to click round and to do the round function. I need to have my values and I need to know how many units I want around to. So I’m gonna click round, Im going to do time estimated minus time tracked. Close that up. I’m going to put a comma and I’m going to say, what decimal place do I want to round to? In this case, I’m going to say the second decimal point. And again, I want to add my label. So I want to make sure that this is inside parentheses in and of itself and then click and, and then type ampersand, space, hours.

[00:03:52.450]
Oh, that’s much better, and that’s how we get to this result, so already we’ve shown you three different formulas here for how to calculate time remaining, depending on how you want to see it and what you prefer. Notice you could also change this to round up if you want to round up to the next highest number.

[00:04:05.200]
All right. Let’s pull over another formula here. This time talking about money, you know, click on it to see the formula. The math that’s happening here is multiplying time tracked by the hourly rate. That’s the main math. And then we’re rounding that result to the nearest one decimal. So that would be point five, point six point one, the nearest decimal point.

[00:04:26.320]
We could also round it to the nearest hole number or the nearest two decimals. Click calculate, we’ll see what happens there, see how that works. If I change it to the nearest one digit, we’ll see that round up. If we change it to the nearest whole number, it’ll look like. By using simple multiplication combined with a rounding, we’re able to get this result to calculate our total earned based on these two columns that we’ve already created.

[00:04:49.870]
Notice what these formula fields, unlike all of our standard and other custom fields, we cannot calculate a sum. We can for hourly rate because it’s just a number that we’re typing, which is crazy, but we cannot actually do that for a formula field. There’s no option here.

[00:05:02.530]
So let’s look at another one here. This one’s just simply asking, is it due today? We have a formula pulling in two elements, the due date, using the field due date, which is available up here in the top left, and then today, so the number of days between due date and today equals zero. If that’s true, it will return, yes, it’s due today. And if it’s not true, it’ll say you’re good. So if we actually look at this, we’ll see that if we scroll over and we see our due dates, this one was due tomorrow, you’re good. Yesterday, you’re good today, yes today. So that’s just another example of how you can use this. Probably not the most practical example, but it is one nonetheless.

[00:05:37.470]
Let’s hide that move on. The next one is talking about days till due. Now, this could be a pretty practical application. I’m going to click on this to show us what the calculation is. This is, again, using the days operator, which we can find on the left side.

[00:05:48.480]
Scroll over it, days requires your end date and your start date, and it’ll give you back the number of days in between. So in this case, we’re going to use days, the field due date, and today. When we calculate that, we end up with a number. Now, just like with hours and hours remaining, we might want to add a label behind it. And so that brings us to this version where we have days, we have due date and we have today. But then we’ve also added on the and days left to let us know exactly what that number is referring to.

[00:06:14.490]
Now, finally, we want to get a little fancier. Maybe we don’t want to have a negative days left when the date is in the past. So we want to add another piece into the formula here. This is just like Excel for those of you who are familiar with those formulas.

[00:06:26.340]
This one is just saying, if the due date from today is more than zero days away, if the difference in days is more than zero, then give us the formula result. But if it’s not, give us the word no. And no matter what response we have, add the words “days left” at the back. And what this does is it says one days left, no days left, three hundred sixty nine days left and so on. First part of the equation is what’s affected by the if function and days left is being added afterwards. And maybe we just want to be a little bit more specific here and say days left.

[00:07:00.960]
OK, one last example here. If you’re still with me, we’re going to click into this formula and we’re going to use another if function, b,ut rather than using words, why not use emojis?

[00:07:09.270]
So this time we’re going to talk about if and we’re going to say if due date is before today, then give us red. And if it’s not, give us green, calculate. Pull this over so we can really see it side by side.

[00:07:24.120]
If a due date is in the past or it’s today, we’re getting a red emoji. And if it’s not any of those, then we have green to let us know if we’re behind or ahead. And this is dynamically pulling in. So something as simple as searching red emoji, copying any search result, pasting that in here is how you can actually go ahead and add these emojis in. Maybe we even went maybe thumbs up, copy your thumbs up emoji, put that in here to let you know that you’re good.

[00:07:51.870]
So this is just one more way that you can use these formula fields to give you a result that makes more sense for the eye. Unfortunately, unlike other custom fields, we can’t sort or group by these. So that’s why it’s kind of nice to have the visual.

[00:08:03.540]
So at least they catch your eye. So those are a bunch of different use cases for formulas. I hope this makes sense. And we’ll put all of these formulas, including some of these lengthy ones in the actual description of this video in case you want to try them out for yourself.

[00:08:14.250]
So in many ways, this custom formula isn’t really a functional custom field in ClickUp it’s treated as very much the exception and not like any other custom field that you have in the system from how you set it up to actually typing into the cell to not being able to sum it up like other custom fields, to not being able to use it for widgets or automation’s, sometimes it can beg the question is why exactly these formula fields even exist.

[00:08:37.020]
Personally, I’d love to see ClickUp build out the rest of the features that is associated with the custom fields.

[00:08:42.180]
So this is actually, you know, at least equal with the other custom fields that we’re used to or spend their energy in making this formula custom field into a widget. That way we can really start reporting on the success of our projects and things that we want to know in a task management system versus more of a row by row formula, which we can’t even sum up or do anything with.

[00:09:02.700]
But if you can afford the mental energy of having things divided, I always recommend you use Excel, AirTable, Chota, Notion, any other external database for your long term storage of information.

[00:09:13.710]
All of those tools have wonderful data visualization that will allow you to actually do stuff with your data more than just creating a dead end formula that you can’t do anything with.

[00:09:22.230]
I definitely do not have the monopoly on information here. So if any of you have a good use case for formulas or a particular formula that you enjoy using or found really helpful in your Workspace, I’d love for you to share it below in the comments so that way others can learn from your wisdom.

[00:09:35.010]
Now, I think that’s it for this video. If you click, subscribe and hit the bell right next to subscribe button, you will be notified when new videos like this come out, which is twice a week every week and until next time, enjoy the process.

Do you love ProcessDriven? Get paid for it! 💰