CODEWITHSUNDEEP
excelworksheet-function
Georox
Georox

Reputation: 1

Comparing dates on excel

I know this is probably very simple but I'm struggling to find a formula that works:

I have two worksheets, one showing annual leave for staff and one showing project dates. How can I highlight where an employee's leave will overlap with a project? There's a high number of staff and projects (with various different dates) so I'm hoping not to check them all manually!

Upvotes: 0

Views: 501

Answers (2)

luispa
luispa

Reputation: 323

  1. Select the dates that you want to highlight:

enter image description here

  1. Click on HOME and then on Styles menu area, click Conditional Formatting

enter image description here

  1. Select New Rule

enter image description here

  1. Select Format only cells that contain and you can play with less than and greater than and put a format for each.

enter image description here

  1. At the end you might have some like this, y use the D2 cell to compare:

enter image description here

Upvotes: 1

J. Chomel
J. Chomel

Reputation: 8395

You can do it with a simple logical AND (its easier in Excel if you multiply).

Represent the worked days of an employee on a single line, one cell per day. For a year this is 365(366) days. If employee is here, then its a '1';

Do the same for each project. If project is "one", then its a '1'.

Then on a third sheet (e.g. one sheet per project), you multiply employee per project. Your matches will be represented as 1, which you can color using formatting functions.

Thus your function for B2 in your Sheet "Staff for project 1" will be:

=projs!B$2*staff!B2

You can "drag" this formula to have it for the whole year and the whole staff.

Then your B2 in your Sheet "Staff for project 2" will be:

=projs!B$3*staff!B2

and so on. the $3 forces the formula to "stay" on the same line when you "drag" the formula.

Here is how it will look: enter image description here

Now your problem becomes: how do I make my lists of dates generate tabs staff and projs

Upvotes: 0

Related Questions