Build a Visual Clock

Intermediate

About this Tutorial

I'll show you how to create a visual clock in your Google Sheet using conditional formatting.

By following a few simple steps, you'll be able to see the progress of each hour throughout the day.

No manual updates required! I'll guide you through setting up the custom formula, applying it to the desired range, and making it work for multiple columns if needed.

Plus, I'll share a tip on how to reverse the colors if you prefer to track the remaining time instead.

Let's get started and make your Google Sheet even more efficient!

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

NOW() TIMEVALUE()

Video Transcript

<div>0:00 So let's say you value your time and you want to look at all 24 hours and you want to make sure that you're hitting maybe some milestones each hour or you just want to keep a sort of a visual clock in your Google Sheet. 0:13 I'm going to show you how to do this to right now. It is 9.57 a.m. And so the top 10. 0:20 Cells here are colored. This is all using additional formatting and it's done automatically. I don't need to do anything else. 0:26 Just set this up and done. I'm going to show you how to do that right now.

So first off, let's get a clean sheet and we have nothing here. 0:33 It's just text each of the hours, midnight to midnight, hour one through 24 and all we're going to do is. 0:40 Highlight the columns are going to go up to format conditional formatting and over here and our conditional formatting . We're going to change the format rules to custom formula and we're going to type in equals time value and what we want to do, we're applying it to the range a one to be so we want  1:00 to know. Is the time value in a one?

What's the time value there and is it less than the time value of now and that's another parentheses. 1:12 This is another function now and there's time value here as well. And then in order to get a and b column both we need to. 1:21 Add a dollar sign to this a one. That may make sure that it's absolute not relative. We're going to click done. 1:28 But over here we have our timing now. That's it. That's all we have to do. So just to reiterate that we're using two time values were using a custom formula . 1:38 We're applying it to the range of a and.

B, we can apply this to C as well. If we want, we can also change this a one with a dollar sign . 1:50 We don't have to use the dollar sign and now only a one is going to work there. And we put the dollar sign . 1:58 That means it's absolute in comparison between. C column B column all points to the a column. That's what it's saying there. 2:06 And we're using greater than or less than if you want to know if you want the instead of the colors going up, you want to know what else do you have left in the day. 2:16 You can just reverse this and say greater than time value.

And now click done. And you can see here we have highlighted all the rest of the hours. 2:26 You can also change the colors down here. There you go. I like it the other way a little bit. And maybe have a more bluish kind of tint to it. 2:37 But yeah, that's it. So I created a 24 hour clock visual 24 hour clock inside of. All with conditional formatting .</div>