Attendance Tracker

[TN77M]

Forum Members,

I'm new to numbers and would like some assistance in using conditional formatting.
I’m building a sheet to track due dates ignorer to track a 45 day mark, 60 and 90 day mark, from initial notification date

Example:

(A3+45); this will give me a date which is 45 days in the future form initial notification date.

(A4+60), this will give me a date which is 60 days in the future from initial notification date.

(A5+90), this will give me a date which is 90 days in the future from initial notification date.

Initial date is manually inputed into cell A1

If the initial notification date is 15 July 2017, then 45 day = 29 Aug 2017; 60 day = 13 Sept 2017; and a 90 day = 13 Oct 2017.

Attendance date is manually inputed into cell A2

I have set cell A7 with “() NOW”

2. Need help with conditional formatting

If “Attendance date” when entered is equal to or greater than 45 day mark then strike out and fill blue. If 45 day mark is exceed but no “attendance date” is entered then strike out and fill with blue.

If “Attendance date” when entered is equal to or greater than 60 day mark then strike out and fill orange. If 45 day mark is exceed but no “attendance date” is entered then strike out and fill with blue.

If “Attendance date” when entered is equal to or greater than 90 day mark then strike out and fill red. If 90 day mark is exceed but no “attendance date” is entered then strike out and fill with red.

If “Attendance date” when entered is less than 45 day mark then stop rules stated above

If “Attendance date” when entered is less than 60 day mark then stop rule for 90 day mark but apply rule for 45 day mark.

If “Attendance date” when entered is less than 90 day mark then stop rule for 90 day and apply 45 and 60 day rule.

If “Attendance date” when entered is not inputed on the 90 day mark then apply rules for 45, 60, 90 day and input "stop" on cell containing “Attendance date”

Thank you indavance

[AliGW]

Welcome to the forum!

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[TN77M]

Note: Using Microsoft Excel for Mac 2016
The attendance date and notification date should be inputted manually and is the driving factor for the other dates.

Conditional Formatting One:

If the attendance date “9/15/17 (B2)” is greater than or equal to the 45-day mark (C2) then strike out C2 and not the 60-Day mark (D2) and 90-Day (E2).
Then rules would stop for this condition.

Conditional Formatting Two:

If the attendance date “9/15/17 (B2)” is greater than or equal to 45-day mark (C2) then strike out C2 and greater than or equal to 60-Day mark (D2) then strike out D2 but do not strike out or color
code E2. Then rules would stop for this condition.

Conditional Formatting Three:

If the attendance date “9/15/17 (B2)” is greater than or equal to 45-day mark (C2) then strike out C2 and greater than or equal to 60-Day mark (D2) then strike out D2 and greater than or equal do E2 then strike out and color code. Add word “STOP” to G2. Then rules would stop for this condition.

Conditional Formatting Four:
If the attendance date has no date entered manually and 90 days have elapsed since notification date then add word “STOP” to G2. Strike out all dates.
Then rules would stop for this condition.

See attachments for further detail.

Thank you in advance