Conditional Formatting IF AND

CordingBags

New Member
Joined
Mar 7, 2022
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Have a spreadsheet with times in one column and dates in another.
Trying to remind the user that if there is a date in one column then there must be a time in the other.

Have used both =NOT(ISBLANK(F2)) and =(ISBLANK(F2)) both of which provide a bit of a messy workaround

Ideally I would have a conditional formatting rule formula that clears the fill colour if the itself is populated.

ie IF CELL F2 has a date, CELL E2 highlights until it also has a time.

Logic IF cell F2 has a date AND this cell (E2) is blank then highlight E2.

Notes: Both columns are blank at the beginning of the process, both columns are DATA Validation controlled to ensure only a date in one and time in the other is accepted.

The more difficult challenge is to reverse the process in column F where the dates already have a conditional formatting rule to ensure that each one is greater than the entry above.
So I very much suspect that whilst a clever formula may allow for the IF AND logic it will not also accommodate the is greater than previous.
However a missing date against a time is less important.


Appreciate any help

Thanks

Paul
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
this should work for a blank time , where there is a date entry
=AND($F2<>"", $E2="" )

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
E2:E100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND($F2<>"", $E2="" )

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

how does the greater than previous date work - does that also use the time , i assume you would get the same date entry a few times ??

maybe we need an example
Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
Solution
this should work for a blank time , where there is a date entry
=AND($F2<>"", $E2="" )

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
E2:E100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND($F2<>"", $E2="" )

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

how does the greater than previous date work - does that also use the time , i assume you would get the same date entry a few times ??

maybe we need an example
Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

You can also test to see if it works ok, in the "Test Here" forum.

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
The suggested Conditional Formatting for the TIME column worked with a little tweaking, =AND(F5<>"", E5="" ), had to remove the absolute references in order to copy it around the workbook.
I have tried to create an XL2BB but not sure how successful I have been to show the greater challenge in the DATE column.

Is this what XL2bb should look like?

Date and Time checks.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1DayTimeDate
2Cell D2 reports the day of the weekThu14/09/2023Cell F2 is Data Validated to ensure it is greater than today
3Highlighting but accepting a weekend fixtureThis row is deliberately blank
4DayTimeDate
5Cell D5 reports the day of the weekSat14:3016/09/2023Cell F5 is Conditionally Formatted to ensure entry is greater than F2
6Again this is replicated down to row 300+ etc.I only use conditional formatting rather than Data Validation as there are occasions when the same date is used
7DayTimeDate
8Sun17/09/2023Cell F8 is Conditionally Formatted to ensure entry is greater than F5
9As above this is replicated down to row 300+ and again on multiple tabs
10DayTimeDate
11 
12
13DayTimeDateTime column is conditionally formatted using =AND(F5<>"", E5="" ), had to remove ABSOLUTE $ reference in order for this formula to copy around the work book
14 18:30
15Date column is all in highlight at commencement as Conditional Formatting does not seem to be able to ignore blanks.
16DayTimeDateIdeally the Date column would start unfilled, but then to colour if either there is a time in column E or if it is not greater than the previous date.
17 
18
19DayTimeDate
20 
21
22DayTimeDate
23 
24
25DayTimeDate
26 
27
28DayTimeDate
29 
30
31DayTimeDate
Sheet1
Cell Formulas
RangeFormula
D2,D29,D26,D23,D20,D17,D14,D11,D8,D5D2=IF(F2<1,"",WEEKDAY(F2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E29Expression=AND(F29<>"", E29="" )textYES
E26Expression=AND(F26<>"", E26="" )textYES
E23Expression=AND(F23<>"", E23="" )textYES
E30Expression=NOT(ISBLANK(E30))textYES
E27Expression=NOT(ISBLANK(E27))textYES
E24Expression=NOT(ISBLANK(E24))textYES
E20Expression=AND(F20<>"", E20="" )textYES
E17Expression=AND(F17<>"", E17="" )textYES
E14Expression=AND(F14<>"", E14="" )textYES
E11Expression=AND(F11<>"", E11="" )textYES
E8Expression=AND(F8<>"", E8="" )textYES
E5Expression=AND(F5<>"", E5="" )textYES
E2Expression=AND(F2<>"", E2="" )textYES
D29Cell Value=1textYES
D29Cell Value=7textYES
D26Cell Value=1textYES
D26Cell Value=7textYES
D23Cell Value=1textYES
D23Cell Value=7textYES
D20Cell Value=1textYES
D20Cell Value=7textYES
D17Cell Value=1textYES
D17Cell Value=7textYES
D14Cell Value=1textYES
D14Cell Value=7textYES
D11Cell Value=1textYES
D11Cell Value=7textYES
D8Cell Value=1textYES
D8Cell Value=7textYES
D5Cell Value=1textYES
D5Cell Value=7textYES
E21Expression=NOT(ISBLANK(E21))textYES
F30Expression=NOT(ISBLANK(F30))textYES
D30Expression=NOT(ISBLANK(D30))textYES
F27Expression=NOT(ISBLANK(F27))textYES
D27Expression=NOT(ISBLANK(D27))textYES
F24Expression=NOT(ISBLANK(F24))textYES
D24Expression=NOT(ISBLANK(D24))textYES
F21Expression=NOT(ISBLANK(F21))textYES
E21Expression=NOT(ISBLANK(E21))textYES
D21Expression=NOT(ISBLANK(D21))textYES
F18Expression=NOT(ISBLANK(F18))textYES
E18Expression=NOT(ISBLANK(E18))textYES
D18Expression=NOT(ISBLANK(D18))textYES
F15Expression=NOT(ISBLANK(F15))textYES
E15Expression=NOT(ISBLANK(E15))textYES
D15Expression=NOT(ISBLANK(D15))textYES
F12Expression=NOT(ISBLANK(F12))textYES
E12Expression=NOT(ISBLANK(E12))textYES
D12Expression=NOT(ISBLANK(D12))textYES
F9Expression=NOT(ISBLANK(F9))textYES
E9Expression=NOT(ISBLANK(E9))textYES
D9Expression=NOT(ISBLANK(D9))textYES
F6Expression=NOT(ISBLANK(F6))textYES
E6Expression=NOT(ISBLANK(E6))textYES
D6Expression=NOT(ISBLANK(D6))textYES
F3Expression=NOT(ISBLANK(F3))textYES
E3Expression=NOT(ISBLANK(E3))textYES
D3Expression=NOT(ISBLANK(D3))textYES
F29Cell Value<=F26textYES
F26Cell Value<=F23textYES
F23Cell Value<=F20textYES
F20Cell Value<=F17textYES
F17Cell Value<=F14textYES
F14Cell Value<=F11textYES
F11Cell Value<=F8textYES
F8Cell Value<=F5textYES
F5Cell Value<=F2textYES
D2Cell Value=1textYES
D2Cell Value=7textYES
F2Cell Value<TODAY()textYES
Cells with Data Validation
CellAllowCriteria
D8List=$A$1
D11List=$A$1
D14List=$A$1
D17List=$A$1
D23List=$A$1
D29List=$A$1
F19:F20Datebetween 01/01/2023 and 31/12/2100
F25:F26Datebetween 01/01/2023 and 31/12/2100
D5List=$A$1
E5Timebetween 00:01:00 and 23:59:00
F5Datebetween 01/01/2023 and 31/12/2100
D20List=$A$1
E20Timebetween 00:01:00 and 23:59:00
D26List=$A$1
E26Timebetween 00:01:00 and 23:59:00
E1Timebetween 00:01:00 and 23:59:00
E7Timebetween 00:01:00 and 23:59:00
F7:F8Datebetween 01/01/2023 and 31/12/2100
E8Timebetween 00:01:00 and 23:59:00
E16Timebetween 00:01:00 and 23:59:00
F16:F17Datebetween 01/01/2023 and 31/12/2100
E17Timebetween 00:01:00 and 23:59:00
E10Timebetween 00:01:00 and 23:59:00
F10:F11Datebetween 01/01/2023 and 31/12/2100
E11Timebetween 00:01:00 and 23:59:00
E13Timebetween 00:01:00 and 23:59:00
F13:F14Datebetween 01/01/2023 and 31/12/2100
E14Timebetween 00:01:00 and 23:59:00
E22Timebetween 00:01:00 and 23:59:00
F22:F23Datebetween 01/01/2023 and 31/12/2100
E23Timebetween 00:01:00 and 23:59:00
E28Timebetween 00:01:00 and 23:59:00
F28:F29Datebetween 01/01/2023 and 31/12/2100
E29Timebetween 00:01:00 and 23:59:00
E31Timebetween 00:01:00 and 23:59:00
F31:F32Datebetween 01/01/2023 and 31/12/2100
D2List=$A$1
E2Timebetween 00:01:00 and 23:59:00
F2Date>TODAY()
.

Much appreciate the help
Thanks
Paul
 

Attachments

  • Date  Time Screenshot (Small).jpg
    Date Time Screenshot (Small).jpg
    67.7 KB · Views: 11
Upvote 0
a blank is seen as a date as dates are just number so to stop that just add an
AND( cell <> "", rest of rule.......

a similar formula for testing if a date is blank and time entered

=AND(E5<>"", F5="" )
Not sure why you choose F5 , when data starts at F2
also loads of conditional formatting rules used - you can apply to a range , dont need to add to every cell

then to test for the date

something like

=AND(F2<>"",F2<>"date",F2<MAX($F$2:F2))
and applied to F2toF100

so i only have 1 conditional formatting rule - but have set to ignore cells with DATE in and also blanks

Book6
ABCDEF
1Date
29/14/23
3
4Date
59/16/23
6
7Date
89/17/23
9
10Date
118/16/23
12
13Date
14
15
16Date
17
18
19Date
20
21
22Date
23
24
25Date
26
27
28Date
29
30
31Date
32
33
34
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F100Expression=AND(F2<>"",F2<>"date",F2<MAX($F$2:F2))textYES


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
F2:F100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(F2<>"",F2<>"date",F2<MAX($F$2:F2))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
 
Upvote 0
a blank is seen as a date as dates are just number so to stop that just add an
AND( cell <> "", rest of rule.......

a similar formula for testing if a date is blank and time entered

=AND(E5<>"", F5="" )
Not sure why you choose F5 , when data starts at F2
also loads of conditional formatting rules used - you can apply to a range , dont need to add to every cell

then to test for the date

something like

=AND(F2<>"",F2<>"date",F2<MAX($F$2:F2))
and applied to F2toF100

so i only have 1 conditional formatting rule - but have set to ignore cells with DATE in and also blanks

Book6
ABCDEF
1Date
29/14/23
3
4Date
59/16/23
6
7Date
89/17/23
9
10Date
118/16/23
12
13Date
14
15
16Date
17
18
19Date
20
21
22Date
23
24
25Date
26
27
28Date
29
30
31Date
32
33
34
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F2:F100Expression=AND(F2<>"",F2<>"date",F2<MAX($F$2:F2))textYES


for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
F2:F100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=AND(F2<>"",F2<>"date",F2<MAX($F$2:F2))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK
Many Thanks for your help, yes got there.
Cheers
Paul
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,596
Members
452,657
Latest member
giadungthienduyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top