Days Remaining in Contruction Gantt Sheet

Hello,
And thank you in advance for any assistance.
I am putting together a sheet that will track construction projects. The sheet includes a Gantt Chart (Sort of).
I hope I can explain this correctly.
The sheet has the following columns: Project, Days Duration, Start Date, End Date. The time between the Start and End dates are reflected in the Gantt chart to the right of the sheet where each box corresponding to a specific day between the dates is colored.
What I would like to do is add a column "Days Remaining" that will indicate the number of days remaining to complete the project based on the Start and End dates. The start dates for project (one per row) are different for each, so the Gantt chart shows they start in different dates. The column Remaining Days would have to do a count down once today's date reaches the Start Date and each day that passes.
I watched many videos, but I did not find one that does this type count down once a date is reached.
Thank you in advance for any assistance.
Capture.PNG

Days Remaining =MAX(0,End Date-TODAY())

Thanks Phuocam.
I did a test with Start Date 10 Nov 2016, End Date 1 Dec 2016, and it returned Days Remaining = 16, which is correct but I need it to only return workdays or weekdays. It needs to not count Saturdays and Sundays. There are 6 weekend days between 10 Nov and 01 Dec, so the true number of workdays it should return should be 10.
So it has to it has to also subtract weekends and holidays. The holidays are derived from a list I put together.
Can you help?

Perfect John. That works. Much appreciated.
Cheers!

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Actually, I have a follow up.
The Gantt chart is properly coloring the days of the project, weekends, and holidays.
Is there a way for it to also color in a different color (red) the days past on a project? So, for example, Project1 is from 10Nov to 1Dec, actual work days are colored blue, weekends and holidays are colored yellow. I want days that pass between the project's Start Date and End Date to change to red.
I am using the following Conditional Formatting formulas:
=AND(F$1>=$C2,F$1<=$D2) this changes cells to blue
=AND(AND(F$1>=$C2,F$1<=$D2),OR(F$1=Holidays!$A$3:$A$39,WEEKDAY(F$1,2)>5)) this changes cells to yellow

Thanks

Try

Please Login or Register  to view this content.

Add spaces to get round firewall!!

Thank you again John.
That worked.