Is it possible to flag a cell if the time in the cell is more than an hour late? Today, in Episode #1444, Bill shows us a Conditional Formatting Formula to answer this question.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1444 – Time > 1 Hour Ago?
Oh, this is embarrassing. Joe sent this question in almost a year ago.
I don't know. It got lost in the inbox over there.
Joe wants to put in time cell A1, and when it's currently an hour after that time, he wants to turn that time red or something like that.
So I'm going to use conditional formatting to do this.
You want to know if we could do it without adding any additional formulas to the worksheet.
Let's just talk about a couple of functions.
There's a function called, =NOW().
And when you enter =NOW(), it’s possible for you to see just the time, so 7:53 AM, but it's actually storing the date and the time.
Let's compare that to =TODAY(), which gives us the date at midnight today.
So what we really need is, we need now minus today, =C1-C2.
That will give us just the time portion, and then you can see we can format that as a time.
And good to go.
So then what we need to do is, we need to compare that formula to 1/24, that's one hour.
And let's format that just as a number, a whole bunch of decimal places, help now equal 1/24.
There you go.
So I kind of built this formula over here.
You know, at any time I'm going to do the formula in conditional formatting, I want to build it in the spreadsheet to make sure it's working.
So =NOW()-TODAY()-A1>(1/24).
And I can actually use my tick, I'll press F9 there to evaluate that.
Sp that's the formula I want to use.
Ctrl C to copy it to the clipboard, and then come back here, Alt+O+D for conditional formatting.
I want to do a new rule, use a formula, and the formula is going to be, Ctrl V. What's the format?
We'll just use a red fill and a white font in bold.
Click OK, OK, OK.
Okay, now we have a couple of options.
We could sit here, it's 7:35.
We can sit here until 8:30 and see if the thing turns red, or we can just back date this say, to up saying 6:30 AM and you'll see that it will turn red.
Now the hassle is, you have to be working in Excel to have this work because that will not recalculate on its own.
Either you have to enter something or press F9.
So if you just, you know, we're going to meditate for an hour, and you wanted it to turn red when you should stop.
It's not going to do it.
You have to have something there.
You have to be working in Excel because as long as you're entering things in Excel, you see that like that time right there will update.
It'll stay at 7:55:48 for a long, long, long, long time, but once I enter something then it updates.
As long as you're working in Excel, this will work.
If you're just leaving the screen blank, it’s not going to work at all.
All right.
Hey, I'm recording this ahead.
I’m actually down in Florida when you see this.
So I hope you're having a great day wherever you are.
I want to thank Joe for sending that question in, and sorry it took me a year to get to it.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1444 – Time > 1 Hour Ago?
Oh, this is embarrassing. Joe sent this question in almost a year ago.
I don't know. It got lost in the inbox over there.
Joe wants to put in time cell A1, and when it's currently an hour after that time, he wants to turn that time red or something like that.
So I'm going to use conditional formatting to do this.
You want to know if we could do it without adding any additional formulas to the worksheet.
Let's just talk about a couple of functions.
There's a function called, =NOW().
And when you enter =NOW(), it’s possible for you to see just the time, so 7:53 AM, but it's actually storing the date and the time.
Let's compare that to =TODAY(), which gives us the date at midnight today.
So what we really need is, we need now minus today, =C1-C2.
That will give us just the time portion, and then you can see we can format that as a time.
And good to go.
So then what we need to do is, we need to compare that formula to 1/24, that's one hour.
And let's format that just as a number, a whole bunch of decimal places, help now equal 1/24.
There you go.
So I kind of built this formula over here.
You know, at any time I'm going to do the formula in conditional formatting, I want to build it in the spreadsheet to make sure it's working.
So =NOW()-TODAY()-A1>(1/24).
And I can actually use my tick, I'll press F9 there to evaluate that.
Sp that's the formula I want to use.
Ctrl C to copy it to the clipboard, and then come back here, Alt+O+D for conditional formatting.
I want to do a new rule, use a formula, and the formula is going to be, Ctrl V. What's the format?
We'll just use a red fill and a white font in bold.
Click OK, OK, OK.
Okay, now we have a couple of options.
We could sit here, it's 7:35.
We can sit here until 8:30 and see if the thing turns red, or we can just back date this say, to up saying 6:30 AM and you'll see that it will turn red.
Now the hassle is, you have to be working in Excel to have this work because that will not recalculate on its own.
Either you have to enter something or press F9.
So if you just, you know, we're going to meditate for an hour, and you wanted it to turn red when you should stop.
It's not going to do it.
You have to have something there.
You have to be working in Excel because as long as you're entering things in Excel, you see that like that time right there will update.
It'll stay at 7:55:48 for a long, long, long, long time, but once I enter something then it updates.
As long as you're working in Excel, this will work.
If you're just leaving the screen blank, it’s not going to work at all.
All right.
Hey, I'm recording this ahead.
I’m actually down in Florida when you see this.
So I hope you're having a great day wherever you are.
I want to thank Joe for sending that question in, and sorry it took me a year to get to it.
I want to thank you for stopping by.
I will see you next time for another netcast from MrExcel.
