Replacing Blank Cells In Columns with a Date

[sfmalone2001]

Hi All,

I have raw data that I pull from ServiceNow for the company I work at. One of the fields is Date Closed and unless the ticket is actually closed, it returns an empty cell which is scattered throughout about 10,000 records.

I copy the raw data into a Table, but before I can refresh my pivot tables which I have built I have to complete the following process:

I select Data
Select only the Blank Cells in the Date Closed Column
Enter =today() and press enter
The blank cells then populate with the current date
Deselect the Data Filter
Select Refresh all.

What I am trying to do is create some sort of formula on the first row of the table which says something like, if the cell is empty or blank, then to populate it with the current date. I just recently purchased a couple of books I have been reading by Bill & Michael, (Pivot Tables 2019) & VBA & Macro Scripting. I haven't gotten to the VBA book yet and about midway through the Pivot Table book which I have already began redoing some of my tables.

Any help would be greatly appreciated.

Shawn

 

[Fluff]

Hi & welcome to MrExcel, you cannot do it with a formula, but you can do it with a macro like this.

  Sheets("Sheet1").ListObjects("Table1").ListColumns("Date Closed").DataBodyRange.SpecialCells(xlBlanks).Value = Date

Change sheet name, table name & column name to suit

 

[sfmalone2001]

Fluff,

Thank you so much.

Shawn

 

[Fluff]

You're welcome & thanks for the feedback.