Data Validation or Conditional Formatting?

[STBTC]

I have a spreadsheet that has the dates from 2017.01.01 to to 2017.12.31 in column A. Columns B, C, & D may or may not have a value in them. For example: On January first, I put a value only in column C for that day. I need a formula that, when the next day comes, Excel will see that no values were put in columns for the previous day—in this example, columns B & D don’t have a value—and would automatically put a value of "0" in the cells. I surmise an IF function that would use the syntax TODAY() in the formula would be the correct route, but I don’t know how to begin.

=IF(TODAY()-1 is all I have with which to begin.

Any help?

[JeteMc]

Paste the following formula into B2 then drag over to D2:

=IF($A2=TODAY()-1,0,"")

At this point you can double click the fill handle on D2 and all three columns will be filled with the formula down to row 32 (or row 366 if your dates in column A run through 12/31/17). You'll notice that when you type 5 into C2 it will overwrite the formula in that cell only.
Let us know if you have any questions.

[STBTC]

Instead of placing the formula in the cells themselves out of fear that my other coworkers who access the workbook would f*¢k it up, is there a way to put is as a conditional formatting formula? Or, if one couldn’t, is it possible to create a formula for that?

[JohnTopley]

You could use VBA worksheet event macto to enter 0s into a cell. It cannot be done with a formula other than using the formula used by JeteMc.

CF does not enable you to enter a value into a cell.

[STBTC]

How would I do that?

[JohnTopley]

See attached.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Or Target.Column <= 2 Or Target.Column > 4 Then Exit Sub

  On Error GoTo ErrHandler
  Application.EnableEvents = False
  If Target.Column = 3 Then
     If Target.Offset(0, -1) = "" Then Target.Offset(0, -1) = 0
     Else
        If Target.Column = 4 Then
         If Target.Offset(0, -2) = "" Then Target.Offset(0, -2) = 0
         If Target.Offset(0, -1) = "" Then Target.Offset(0, -1) = 0
        End If
     End If
  
ErrHandler:
  Application.EnableEvents = True
End Sub

To insert (or view) code, right click on tab ==>"View Code"

If a complete row is blank i.e day skipped, that row remains blank.

Just enter data into B, C or D

[STBTC]

If a complete row is skipped, instead of it remaining blank, can it be filled with zeroes?

[JohnTopley]

Updated code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Row = 1 Or Target.Column > 4 Then Exit Sub

  On Error GoTo ErrHandler
  Application.EnableEvents = False
  
  If Target.Column = 3 Then
     If Target.Offset(0, -1) = "" Then Target.Offset(0, -1) = 0
     Else
        If Target.Column = 4 Then
         If Target.Offset(0, -2) = "" Then Target.Offset(0, -2) = 0
         If Target.Offset(0, -1) = "" Then Target.Offset(0, -1) = 0
        End If
     End If
  ' Fill any blank rows with 0s ....
  
  Row = Target.Row - 1
  For c = 2 To 4
    For r = 2 To Row
      If Cells(r, c) = "" Then Cells(r, c) = 0
    Next r
  Next c

  
ErrHandler:
  Application.EnableEvents = True
End Sub

[JeteMc]

While I don't think that this is as good of a solution as the VBA that John has proposed, it may be suitable in some cases.
1) Put a zero in B2,
2) Drag the fill handle over to D2,
3) While B2:D2 are selected press Ctrl and the down arrow,
4) While B2:D32 are selected press Ctrl and D,
5) Paste the following formula into conditional formatting (Use a formula to determine which cells to format):

=AND(B2=0,$A2>=TODAY())

6) Choose to format the font as white to match the background.
In the attached file I have selected B16 so that you can see that the value of zero is actually in the cell and will be become visible once the system calendar reaches 12/16/16.
Let us know if you have any questions.