How to Use ClickUp FORMULA Custom Fields (Advanced Examples)

ClickUp now has a Formula Custom field, basically a column in 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? Will it replace tools like Notion, Coda, Excel, Airtable? 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 Formula Custom field is. Now, before we get started, let me say my name is Laila. 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. 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 FX icon and select what you want your formula to do. So for example, maybe 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, 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 scan 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. 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. Well 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. Now, this is great, but it's certainly not ideal. When we were looking at this and we're seeing 1.33. 1.33 what? Hours, days, years, months. 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 and we're going to add in a label field to let us know that it's the number of hours between these. To do this, we need to add the and sign and ampersand and then give it our label. Now, if I click 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. So I'm going to click calculate. All of a sudden now we've got it going, but we've got two more problems. 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. 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. Let's delete everything and start from scratch. We're going to go to round, which I can just start typing or I can scroll for. I'm going 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 to round to. So I'm going to click round. I'm going to do time estimated minus time tracked, close that up. Then I'm going to put a comma and I'm going to say what decimal place do I want it 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. Ah, 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. And notice, you could also change this to round up if you wanted to round up to the next highest number. All right, let's pull over another formula here, this time talking about money earned. Gonna 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 0.5, 0.6, 0.1, the nearest decimal point. We could also round it to the nearest whole number or the nearest 2 decimals. I'm going to click calculate and 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. And if we change it to the nearest whole number, it'll look like that. 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. Notice, with 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 our formula fields. There's no option here. 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'll 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, yesterday. So that's just another example of how you can use this. Probably not the most practical example, but it is one nonetheless. So 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. 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. Now, finally, if 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. 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 1 days left, no days left, 369 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 just say days left. Okay, 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. But rather than using words, why not use emojis? 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 here so we can really see it side by side. 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 want maybe. Thumbs up. Copy your thumbs up emoji. Put that in here to let you know that you're good. 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 them visual 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. So in many ways this custom formula is 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 automations. Sometimes it can beg the question as why exactly these formula fields even exist. Personally I'd love to see ClickUp build out the rest of the features that is associated with a custom field. 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. But if you can afford the mental energy of having things divided, I always recommend you use Excel, Airtable, Coda, Notion, any other external database for your long term storage of information. 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. 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 have 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. Now I think that's it for this video. If you click subscribe and hit the bell right next to the 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.