Excel - IF Function Problem - Expert Solution

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
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.

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.