Question description:
This user has given permission to use the problem statement for this
blog.
IF A1 is greater than 2hrs from B2 so make that row red
Solved by X. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
28/08/2018 - 07:25
Hi
Excelchat Expert
28/08/2018 - 07:25
It's pleasure to help you today.
User
28/08/2018 - 07:26
If A1 is greater than 2hrs from B2 then make the whole row red
Excelchat Expert
28/08/2018 - 07:26
You want to have red colour entire row (first row or second row)?
User
28/08/2018 - 07:27
?
Excelchat Expert
28/08/2018 - 07:27
Hi, I am here.
User
28/08/2018 - 07:27
If A1 is greater than 2hrs from B2 then make the whole row red
Excelchat Expert
28/08/2018 - 07:27
Ok. Please create the sample in the preview.
Excelchat Expert
28/08/2018 - 07:29
Ok. I see the times are in C1 and E1.
User
28/08/2018 - 07:29
If C1 is greater than 2hrs from E1 then make the whole row red
Excelchat Expert
28/08/2018 - 07:29
Ok.
User
28/08/2018 - 07:29
thnx, please do it fast
User
28/08/2018 - 07:30
it's a conditional formatting
User
28/08/2018 - 07:30
required
Excelchat Expert
28/08/2018 - 07:30
yes. I am working on it. Please give me few minutes, while working.
User
28/08/2018 - 07:30
ok
User
28/08/2018 - 07:31
13 Mint left
Excelchat Expert
28/08/2018 - 07:33
=C$1>d$1+time(2,0,0)
Excelchat Expert
28/08/2018 - 07:33
This is the logic, I am following.
User
28/08/2018 - 07:33
ok
User
28/08/2018 - 07:34
come on 10 mint
User
28/08/2018 - 07:34
left
Excelchat Expert
28/08/2018 - 07:34
You can extend the session. No Problem. I am working on it. I have applied the formiula. It is not picking up correctly in Google sheets.
Excelchat Expert
28/08/2018 - 07:35
I am working on local excel file.
User
28/08/2018 - 07:35
OK bro
Excelchat Expert
28/08/2018 - 07:36
=$C1>$D1+time(2,0,0)
Excelchat Expert
28/08/2018 - 07:37
Please refer the formula. "$" fixing location is important. Please check in preview.
Excelchat Expert
28/08/2018 - 07:37
I have used custom formula option in conditional formatting.
User
28/08/2018 - 07:37
How you apply on conditional formatting
Excelchat Expert
28/08/2018 - 07:38
and applied over A1:Z1000. You can change the column of your wish.
Excelchat Expert
28/08/2018 - 07:38
In Home menu, there is an conditional formatting option.
Excelchat Expert
28/08/2018 - 07:38
Please click on that.
User
28/08/2018 - 07:38
hy
User
28/08/2018 - 07:38
M here
User
28/08/2018 - 07:39
wait let me send you the ss
Excelchat Expert
28/08/2018 - 07:39
Click on New Rule.
Excelchat Expert
28/08/2018 - 07:39
And select the last option using custom formula.
Excelchat Expert
28/08/2018 - 07:39
And enter the formula provided.
Excelchat Expert
28/08/2018 - 07:39
Change the applied range as A1:Z1000 or the range you are looking for.
User
28/08/2018 - 07:40
Please check the attachment
[Uploaded an Excel file]
Excelchat Expert
28/08/2018 - 07:40
[Uploaded an Excel file]
Excelchat Expert
28/08/2018 - 07:41
Fix F column.
Excelchat Expert
28/08/2018 - 07:41
and J.
Excelchat Expert
28/08/2018 - 07:41
Change F$1 to $F1
User
28/08/2018 - 07:42
Wait let me check
Excelchat Expert
28/08/2018 - 07:42
=$F1>$J1+Time(2,0,0)
User
28/08/2018 - 07:44
Its make whole row red
Excelchat Expert
28/08/2018 - 07:45
Yes. What is the range you are looking for red? You can change the range A1:Z1000
User
28/08/2018 - 07:45
Check Attachment
[Uploaded an Excel file]
Excelchat Expert
28/08/2018 - 07:45
Please change the formula first.
Excelchat Expert
28/08/2018 - 07:46
=$F5>$J5+Time(2,0,0)
User
28/08/2018 - 07:46
Already did
User
28/08/2018 - 07:46
ok
Excelchat Expert
28/08/2018 - 07:46
I hope, the range starts from row 5.
User
28/08/2018 - 07:46
Ye
Excelchat Expert
28/08/2018 - 07:46
And when you are applying the conditional formatting from 5th row, you have to change formula includes 5th row only.
Excelchat Expert
28/08/2018 - 07:47
As, it is starting point.
Excelchat Expert
28/08/2018 - 07:47
I hope, you got the answer as you are looking for by applying the range from 5th row.
User
28/08/2018 - 07:48
please add one more thing if anyone of the coloumn is blank or less than two hrs then no formatting
Excelchat Expert
28/08/2018 - 07:49
=and($F5>$J5+Time(2,0,0)$F5<>"",$J5<>"")
User
28/08/2018 - 07:49
wait let me apply this
Excelchat Expert
28/08/2018 - 07:49
Change the formula to above. It will check when both columns have data and checks the 2 hours slot.
User
28/08/2018 - 07:50
didn't work bro
User
28/08/2018 - 07:51
Didn't work please check once from your end
Excelchat Expert
28/08/2018 - 07:51
Please check in the preview.
Excelchat Expert
28/08/2018 - 07:51
I have applied the same logic.
User
28/08/2018 - 07:52
Check on sheet
Excelchat Expert
28/08/2018 - 07:52
=and($C5>$D5+time(2,0,0),$C5<>"",$E5<>"")
User
28/08/2018 - 07:53
Check C5 and E5 difference
User
28/08/2018 - 07:53
it's not more than 2hrs
Excelchat Expert
28/08/2018 - 07:53
I am checking.
Excelchat Expert
28/08/2018 - 07:53
Ok.
User
28/08/2018 - 07:54
Please check and update
Excelchat Expert
28/08/2018 - 07:57
=and(($C5-$E5)*24>2,$C5<>"",$E5<>"")
Excelchat Expert
28/08/2018 - 07:57
You can try this formula as shown in preview.
User
28/08/2018 - 07:59
Did n't on my sheet, wait let me share a SS
Excelchat Expert
28/08/2018 - 08:00
What is the formula applied at your end?
Excelchat Expert
28/08/2018 - 08:00
Please change C, E to your columns F and J.
User
28/08/2018 - 08:00
Check this
[Uploaded an Excel file]
Excelchat Expert
28/08/2018 - 08:01
Please change the columns names C, E to F, J
Excelchat Expert
28/08/2018 - 08:01
=and(($F5-$J5)*24>2,$F5<>"",$J5<>"")
User
28/08/2018 - 08:02
let me check
User
28/08/2018 - 08:05
Didn't work damm
User
28/08/2018 - 08:05
should I share a sheet with you
Excelchat Expert
28/08/2018 - 08:05
Please share the file.
User
28/08/2018 - 08:05
Here only
Excelchat Expert
28/08/2018 - 08:05
Ok.
User
28/08/2018 - 08:05
https://docs.google.com/spreadsheets/d/1w3VwggQ_cujUdck8FUvjDN90Tk-6XQc67VjgJC0-Dzs/edit#gid=0
User
28/08/2018 - 08:06
Check it
Excelchat Expert
28/08/2018 - 08:06
Please open the permissions to all.
Excelchat Expert
28/08/2018 - 08:06
We are not supposed to ask for requests.
Excelchat Expert
28/08/2018 - 08:08
Hi, there?
User
28/08/2018 - 08:10
Yes
User
28/08/2018 - 08:10
Sorry
User
28/08/2018 - 08:10
Was wrongly close
Excelchat Expert
28/08/2018 - 08:10
Ok.
User
28/08/2018 - 08:10
I did to public
Excelchat Expert
28/08/2018 - 08:10
Change the sharing settings and set as anyone.
User
28/08/2018 - 08:10
please open the sheet
Excelchat Expert
28/08/2018 - 08:10
Ok.
Excelchat Expert
28/08/2018 - 08:11
I am referring the file.
Excelchat Expert
28/08/2018 - 08:12
F<J.
Excelchat Expert
28/08/2018 - 08:13
You have provided me the condition that F>J. Please change the time in F more than J+2
Excelchat Expert
28/08/2018 - 08:14
Hi, there?
User
28/08/2018 - 08:14
Bro mean to say if there is a difference between more than 2hrs in F and J make it red
Excelchat Expert
28/08/2018 - 08:14
I can't change.
User
28/08/2018 - 08:14
is a condtion
Excelchat Expert
28/08/2018 - 08:14
Ok.
Excelchat Expert
28/08/2018 - 08:14
Then, use absolute formula.
User
28/08/2018 - 08:14
Please do it
Excelchat Expert
28/08/2018 - 08:15
=and(abs($F5-$J5)*24>2,$F5<>"",$J5<>"")
User
28/08/2018 - 08:15
Wait
User
28/08/2018 - 08:17
I have one more query thnx alot for this man, you really did a good job
User
28/08/2018 - 08:17
how to freeze time
User
28/08/2018 - 08:17
mean now
Excelchat Expert
28/08/2018 - 08:17
Great to hear.
Excelchat Expert
28/08/2018 - 08:17
Thank you.
User
28/08/2018 - 08:17
now()
Excelchat Expert
28/08/2018 - 08:17
Have a great day ahead.
Excelchat Expert
28/08/2018 - 08:18
Please do visit Got IT Pro for new queries.
User
28/08/2018 - 08:18
Um ok
User
28/08/2018 - 08:18
thnx buddy
Excelchat Expert
28/08/2018 - 08:18
You are welcome.
Excelchat Expert
28/08/2018 - 08:18
We offer one question per session as per the policies.
Excelchat Expert
28/08/2018 - 08:19
Please visit Got IT Pro for new queries.
User
28/08/2018 - 08:19
Can I get your instagram or Fb account name
Excelchat Expert
28/08/2018 - 08:19
I am sorry. We can's share personal details.
User
28/08/2018 - 08:20
Wait
User
28/08/2018 - 08:20
Bro
Excelchat Expert
28/08/2018 - 08:20
Please visit Got It Pro for new queries. or approach helpdesk in the website for any additional queries.
User
28/08/2018 - 08:20
I think It's not done at all
User
28/08/2018 - 08:20
just give me a seconf
User
28/08/2018 - 08:20
second
User
28/08/2018 - 08:21
Can you open that sheet again and check it
Excelchat Expert
28/08/2018 - 08:22
Hi, We have checked the condition? What is the problem you are facing?
User
28/08/2018 - 08:22
After apply this my rest of formula in sheet didn't work
User
28/08/2018 - 08:22
can you please check
Excelchat Expert
28/08/2018 - 08:23
Yes. I have opened the file.
Excelchat Expert
28/08/2018 - 08:23
It is just conditional formatting that is applied.
Excelchat Expert
28/08/2018 - 08:24
So, it is not related to the problem with formulas. I have provided formula to check F and J column times.
User
28/08/2018 - 08:24
Bro but it's related with this query only
Excelchat Expert
28/08/2018 - 08:24
I have only view only permission.
Excelchat Expert
28/08/2018 - 08:24
I have not touched the sheet.
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.
