Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i am making a call sheet need excel to highlight if call needs to be made in 15 mins
Solved by G. W. in 31 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 10/10/2018 - 12:36
Hello, I understand that you need help with conditional formatting for calls that are to be made within the next 15 minutes, right?
User 10/10/2018 - 12:36
yep
Excelchat Expert 10/10/2018 - 12:36
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert 10/10/2018 - 12:37
Do you have a file we can work on?
User 10/10/2018 - 12:37
yah
Excelchat Expert 10/10/2018 - 12:37
Please upload the file using the clip icon next to this chat.
User 10/10/2018 - 12:39
did that work
[Uploaded an Excel file]
Excelchat Expert 10/10/2018 - 12:39
Thank you.
Excelchat Expert 10/10/2018 - 12:39
Please give me a few minutes to download and analyze the file.
Excelchat Expert 10/10/2018 - 12:40
While I'm doing that, can you explain which sheet and columns should I be looking at?
Excelchat Expert 10/10/2018 - 12:41
Oh just one sheet and one column :)
User 10/10/2018 - 12:41
i just need the a column to turn red if wiith in15 mins of b1
User 10/10/2018 - 12:41
or current time
Excelchat Expert 10/10/2018 - 12:41
Am I to assume that all the time in here are in the current day?
User 10/10/2018 - 12:41
yes
Excelchat Expert 10/10/2018 - 12:42
Alright, I'll be creating the rule for this. Please give me a few minutes while I work on this locally on my end. I'll keep you updated.
User 10/10/2018 - 12:42
ok thanks
Excelchat Expert 10/10/2018 - 12:44
Did you manually type your contact time?
User 10/10/2018 - 12:44
yes
Excelchat Expert 10/10/2018 - 12:45
I'm asking because they are not valid time so Excel does not really understand their true value.
User 10/10/2018 - 12:45
i used 00/:00 formula to auto do :
User 10/10/2018 - 12:45
we use military time
Excelchat Expert 10/10/2018 - 12:46
Yes, but the way you written them is not something Excel understands.
User 10/10/2018 - 12:46
oh ok
Excelchat Expert 10/10/2018 - 12:46
For instance your 04:45 is actually 445 or four hundred and forty-five
Excelchat Expert 10/10/2018 - 12:47
I'll see if I can make something based on this.
User 10/10/2018 - 12:47
thank youo
User 10/10/2018 - 12:47
you
Excelchat Expert 10/10/2018 - 12:48
How would you type 8PM?
User 10/10/2018 - 12:48
2000
User 10/10/2018 - 12:49
i was just trying to avoid having to type the : everytime
Excelchat Expert 10/10/2018 - 12:49
I understand. Unfortunately that changes the way the values are stored. I think I can still make something out of this though. Give me a few minutes.
User 10/10/2018 - 12:49
cool
Excelchat Expert 10/10/2018 - 12:50
What time is it currently there?
User 10/10/2018 - 12:50
0749
Excelchat Expert 10/10/2018 - 12:50
So 800 would have to be highlighted, right?
User 10/10/2018 - 12:50
yes
Excelchat Expert 10/10/2018 - 12:52
Please extend the session when prompted.
Excelchat Expert 10/10/2018 - 12:56
Nearly done.
User 10/10/2018 - 12:56
cool
Excelchat Expert 10/10/2018 - 12:57
If the time has passed? Should it be highlighted as well?
Excelchat Expert 10/10/2018 - 12:57
Or just the ones that are within 15 minutes?
User 10/10/2018 - 12:58
if they can stay highlighted that would be great maybe yellow for > 15 mins and red for past the time
Excelchat Expert 10/10/2018 - 12:58
Okay, this normally falls under our 1 question per session policy but let me see if I can add that quickly.
User 10/10/2018 - 12:59
ok
User 10/10/2018 - 01:00
its ok too if it just stays highlighted
Excelchat Expert 10/10/2018 - 01:03
Done.
Excelchat Expert 10/10/2018 - 01:03
Uploading the file with the instructions.
Excelchat Expert 10/10/2018 - 01:04
Please download this file.
[Uploaded an Excel file]
Excelchat Expert 10/10/2018 - 01:04
I have applied the conditional formatting in Sheet1. I have included a new sheet called INSTRUCTIONS with the detailed steps on how I created the rule.
User 10/10/2018 - 01:05
thank you so much
Excelchat Expert 10/10/2018 - 01:05
Is that what you need?
User 10/10/2018 - 01:05
yes perfect
Excelchat Expert 10/10/2018 - 01:06
That's great! Glad to have helped!
Excelchat Expert 10/10/2018 - 01:06
Would there be anything else that I can help you with regards to the original question?
User 10/10/2018 - 01:06
no i am good have a great day
Excelchat Expert 10/10/2018 - 01:06
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert 10/10/2018 - 01:06
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert 10/10/2018 - 01:06
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User 10/10/2018 - 01:07
No problem for sure a 5

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.