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