Condtional Formatting - Date and Time within 15 minutes of each other

[eli_m]

Hi all,

I currently have this helper column:

It combines two columns together using this formula:

=IF([@[Planning Date]]="N/A","",
IF(ISFORMULA([@[Planning Time]]),"",
CONCATENATE(TEXT([@[Planning Date]],"dd/mm/yy")," ",TEXT([@[Planning Time]],"hh:mm am/pm"))))

I want to make it so that the cells show up RED if any of the rows are less than 30 minutes (29 minutes to be exact) from each other.

What would be the best way and formula for this in conditional formatting?

Thanks in advance!

 

[Jimmypop]

Hi there...

You can try this formula in the Use formula to determine formatting part of CF...

=AND(ROW()>1, ABS($B2-$B1)*1440<29)

 

[eli_m]

Hi there...

You can try this formula in the Use formula to determine formatting part of CF...

=AND(ROW()>1, ABS($B2-$B1)*1440<29)

Hi there!

First off thanks for your help. Thats got me a little confused.

My column is "AY" and my data starts on row: AY3

When I change the CF to:
=AND(ROW()>1, ABS($AY2-$AY1)*1440<29)

It doesn't change the cell colour.

What am I doing wrong here? (probably a lot! haha)

 

[Jimmypop]

Mmmmm In what columns are your planning date and planning time... these are the two that should be subtracted... maybe my test example will help...

Book1.xlsx
	A	B	C
1	Planning Date	Planning Time	Column3
2	22/03/2023	13:45	22/03/2023 01:45 pm
3	22/03/2023	14:00	22/03/2023 02:00 pm
4	22/03/2023	14:45	22/03/2023 02:45 pm
5	22/03/2023	15:00	22/03/2023 03:00 pm
6	22/03/2023	15:28	22/03/2023 03:28 pm
7	22/03/2023	16:00	22/03/2023 04:00 pm
8	22/03/2023	16:05	22/03/2023 04:05 pm
9	22/03/2023	16:25	22/03/2023 04:25 pm
10	22/03/2023	17:25	22/03/2023 05:25 pm
11	22/03/2023	18:25	22/03/2023 06:25 pm
12	22/03/2023	18:30	22/03/2023 06:30 pm
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF([@[Planning Date]]="N/A","", IF(ISFORMULA([@[Planning Time]]),"", CONCATENATE(TEXT([@[Planning Date]],"dd/mm/yy")," ",TEXT([@[Planning Time]],"hh:mm am/pm"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C12	Expression	=AND(ROW()>1, ABS($B2-$B1)*1440<29)	text	NO

 

[eli_m]

Mmmmm In what columns are your planning date and planning time... these are the two that should be subtracted... maybe my test example will help...

Book1.xlsx
	A	B	C
1	Planning Date	Planning Time	Column3
2	22/03/2023	13:45	22/03/2023 01:45 pm
3	22/03/2023	14:00	22/03/2023 02:00 pm
4	22/03/2023	14:45	22/03/2023 02:45 pm
5	22/03/2023	15:00	22/03/2023 03:00 pm
6	22/03/2023	15:28	22/03/2023 03:28 pm
7	22/03/2023	16:00	22/03/2023 04:00 pm
8	22/03/2023	16:05	22/03/2023 04:05 pm
9	22/03/2023	16:25	22/03/2023 04:25 pm
10	22/03/2023	17:25	22/03/2023 05:25 pm
11	22/03/2023	18:25	22/03/2023 06:25 pm
12	22/03/2023	18:30	22/03/2023 06:30 pm
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=IF([@[Planning Date]]="N/A","", IF(ISFORMULA([@[Planning Time]]),"", CONCATENATE(TEXT([@[Planning Date]],"dd/mm/yy")," ",TEXT([@[Planning Time]],"hh:mm am/pm"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C12	Expression	=AND(ROW()>1, ABS($B2-$B1)*1440<29)	text	NO

That looks great and so clear so I don't know how I messed it up:
My date and time is in column U and V and starts on ROW 3 downwards

Column AY holds the CONCENATE:

CF is:

=AND(ROW()>1, ABS($V3-$V2)*1440<29)

is it the CF formula I have wrong?

 

[etaf]

just an idea

are the dates and times - real or just TEXT
right click - format and set to general - you should see a number - integer for dates and decijust mal for time

 

[eli_m]

just an idea

are the dates and times - real or just TEXT
right click - format and set to general - you should see a number - integer for dates and decijust mal for time

Column U:

Column V:

I'd say they are real and not just text

 

[Jimmypop]

Not sure why...will have look when back at PC...

 

[etaf]

as i said, change to General and see if you get a decimal

Book25
	E	F
9	text	General
10	12:34	0.52361111
Sheet2

 

[eli_m]

as i said, change to General and see if you get a decimal

Book25
	E	F
9	text	General
10	12:34	0.52361111
Sheet2

Column U:

Column V:

It's not in decimal - why is that?