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:

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

[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):

Please Login or Register  to view this content.

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.