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