Conditional Formatting using Dates but excluding weekends

[PRIV]

Here is my set up.

Colum E has the date a visit was done and column G is empty until a report is
handed in.

I would like column G to turn red when the report is not handed in within 15
days and to stay red when the date the report was finally put in to show that
it was late.

If there is a way to do this all your help would be mucha appreciated!!!

Thanks in advance

 

[Roger Converse]

You could use one of these formulas. I am assuming you will also want to
exclude holidays. You'll have to create that range and then update it
yearly. You can put that in a worksheet and then hide the worksheet so no
one gets confused.

A1: Date Description
A2: 10/01/2008 Start date of project
A3: 3/01/2009 End date of project
A4: 11/26/2008 Holiday
A5: 12/4/2008 Holiday
A6:1/21/2009 Holiday

Formula Description (Result)

=NETWORKDAYS(A2,A3) Number of workdays between the start and end date above
(108)

=NETWORKDAYS(A2,A3,A4) Number of workdays between the start and end date
above, excluding the first holiday (107)

=NETWORKDAYS(A2,A3,A4:A6) Number of workdays between the start and end date
above, excluding every holiday above (105)

Thanks,
Roger

 

[PRIV via OfficeKB.com]

Hi Roger,

I think this is gonna be alot of work but I'm gonna try it. Since we will
have over 100 sites and from each site there are gonna be tons of reports
that are will come in at different times which means I am gonna have to
figure out which holidays fall where and figure out when each report is due.

Is there a way to create a formula to calculate 15 days after the date of
the scheduled visit?

Much Thanks

You could use one of these formulas. I am assuming you will also want to
exclude holidays. You'll have to create that range and then update it
yearly. You can put that in a worksheet and then hide the worksheet so no
one gets confused.

A1: Date Description
A2: 10/01/2008 Start date of project
A3: 3/01/2009 End date of project
A4: 11/26/2008 Holiday
A5: 12/4/2008 Holiday
A6:1/21/2009 Holiday

Formula Description (Result)

=NETWORKDAYS(A2,A3) Number of workdays between the start and end date above
(108)

=NETWORKDAYS(A2,A3,A4) Number of workdays between the start and end date
above, excluding the first holiday (107)

=NETWORKDAYS(A2,A3,A4:A6) Number of workdays between the start and end date
above, excluding every holiday above (105)

Thanks,
Roger

Here is my set up.

[quoted text clipped - 8 lines]

Thanks in advance