How to Auto-Assign Due Date, (with conditional formatting)?

Question

I am creating a project tracking spreadsheet. My team will all have access, and will enter their projects' START dates in a cell.

I need the cell next to the START date to automatically fill the DUE Date, for a specified range (e.g. 2 weeks after the specified START date). Also, I would like these cells to have conditional formatting (i.e. turn RED) when the deadline has passed.

For example:

PROJECT START DATE	PROJECT DUE DATE
02/04/2020	02/18/2020

Also, not necessary, but it would be extra helpful if the DUE DATE cells could be locked (i.e. nobody can change the date or delete the formula....)

Any assistance with this matter would be greatly appreciated - thank you!

Answer 1

Step 1: Unlock cells.

• Select all cells that the user should be able to change, in particular the start date range.
• Press Ctrl+1 to activate the 'Format Cells' dialog.
• Activate the Protection tab.
• Clear the Locked check box.
• Click OK.

Step 2: Conditional formatting.

• Select the due date range. In the following, I'll assume that it is B2:B1000, and that B2 is the active cell within the selection.
• On the Home tab of the ribbon, select Conditional Formatting > New Rule...
• Select 'Format only cells that contain'.
• Leave the first dropdown set to 'Cell Value'.
• Select 'less than' from the second dropdown.
• Enter =TODAY() in the box next to it.
• Click Format...
• Specify red as font color.
• Click OK, then click OK again.

Step 3: Protect the sheet.

• Right-click the sheet tab.
• Select 'Protect Sheet...' from the context menu.
• Use the check boxes to specify what the user will be allowed to do.
• At the very least, leave 'Select unlocked cells' ticked (And 'Protect worksheet...' of course).
• If you want to specify a password that will be needed to unprotect the sheet, enter it in the Password box. Don't forget the password!
• Click OK. If you specified a password, you'll have to enter it again as confirmation.

Step 4: VBA code.

• Right-click the sheet tab.
• Select 'View Code' from the context menu.
• Copy the following code into the worksheet module:

 Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range

    If Not Intersect(Range("A2:A1000"), Target) Is Nothing Then

        Application.ScreenUpdating = False

        Application.EnableEvents = False

        Me.Unprotect Password:="secret"

        For Each rng In Intersect(Range("A2:A1000"), Target)

            If IsDate(rng.Value) Then

                rng.Offset(0, 1).Value = rng.Value + 14

            Else

                rng.Offset(0, 1).ClearContents

            End If

        Next rng

        Me.Protect Password:="secret"

        Application.EnableEvents = True

        Application.ScreenUpdating = True

    End If

End Sub

• Change the range A2:A1000 to the range of start dates.
• Change "secret" to the password that you used. If you didn't use a password, use "".
• Switch back to Excel.

5. Final steps.

• Save the workbook as a macro-enabled workbook (.xlsm).
• Users should allow macros when they open the workbook.

Answer 2

Hi

Please, try the following

1- I just wonder if you could insert a column for the "Duration of the Project" just in case you might have projects with different periods of completion

2- Then your formula will be =B2+C2 and fill down

3-For the Conditional Formatting

a) Select all the cells you want to apply the CF

b) and use formula    =$D2<TODAY()

Please note,  Only Column must be locked with $, but not the row

3- To lock DUE DATE cells, Please check this video

https://www.youtube.com/watch?v=wrOE8fheMR0

Hope this helps you.

Do let us know if you need more help

Regards

Jeovany

Answer 3

Hi,

Have you checked the above suggestions? You are welcome to post back to let us know if they are helpful to you, your response could also help those who have the same question in this community.

Best Regards,

Arck