Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

conditional formatting cell empt

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Conditional formatting when a cell is empty after a set date

Conditional formatting when a cell is empty after a set date

ResolvedVersion 2016

Stephanie has attended:
Excel Introduction course
Excel Intermediate course

Conditional formatting when a cell is empty after a set date

Hi

I am trying to work out how to get a cell to highlight if it is empty after a certain date (which is located in a different cell) as it means a timesheet has not been handed into us. As an example:
I have Monday 7 January in cell A1 and would input the amount of hours worked in cell A2/ Tuesday 8 January in cell B1 and would input amount of hours in cell B2 and so on. The deadline for submitting a timesheet is Tuesday of the following week (so Tuesday 15 January for this example). If I do not have any data in the box for amount of hours after the deadline (so from Wednesday 16 January), I would like it to highlight red so we can see right away who is late.
In addition to this, I will also be looking to have running total of late timesheets by using conditional formatting/a formula to count if a cell has been highlighted. I have managed to get it to count if the cells have something written in but not if it is highlighted so it is doing opposite of what I am looking for (formula used is =COUNTA(A2:B2) )

Any help would be greatly appreciated!

Stephanie

RE: Conditional formatting when a cell is empty after a set date

Hi Stephanie,

Thank you for a interesting forum question.

The =COUNTBLANK(A2:B2) function will count blanks in a range, but you want to count red cells in a range.

It is not an easy task, but please have a look at the attached workbook. I have added conditional formatting if cells are blank after Tuesday the following week. I have used the Sumproduct function to count blanks if cells are blank after Tuesday the following week.

I hope this makes sense




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

conformatoverdue.xlsx

RE: Conditional formatting when a cell is empty after a set date

I have been playing a bit and it seems to be working -fingers crossed and thanks so much!!

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

RE: Conditional formatting when a cell is empty after a set date

I am sorry if I am being super silly here but I cannot see an attachment at the bottom

RE: Conditional formatting when a cell is empty after a set date

Ignore me - it just came up when the page refreshed :)

Fri 1 Feb 2019: Automatically marked as resolved.

Excel tip:

New to Excel 2010 - Sparklines!

Excel 2010 includes a new feature called Sparklines which are tiny charts that fit into a single cell and plot data in cells from the worksheet. There are a host of formatting and styles that can be applied to them and they are really quite interesting.

>insert
>sparklines
>Choose any style you want

You will be asked for the range and it will automatically select the cell your in to insert the sparklines.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.