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.