Insert default date into blank cells in date column

**deleter** · 07-31-2017, 02:40 PM

Hi everyone, I have this column that lists dates in d/m/yyyy format. However some of the cells have no dates. How am I able to automatically insert a default date (e.g. 1/1/1111) into the blank cells of this date column? Any suggestions are much appreciated!

defaultdate.PNG

**Syrkrasi** · 07-31-2017, 03:47 PM

Try this

Please Login or Register  to view this content.

See Attachment

**deleter** · 07-31-2017, 03:52 PM

Hi Thank you for the reply.

Please Login or Register  to view this content.

I like this, however, for this project specifically, some records don't have a date at all, for these I was thinking of inserting a "dummy date" just as a reminder to the user that, that record has no date (e.g. 1/1/1111). So instead of "=Today()" could something like "=1/1/1111" work? Also, I have a macro that was used previously to split various columns (thanks to DanielExcelVBA), would including new code like this potentially disrupt the present macro's functionality? Thank you.

Please Login or Register  to view this content.

**deleter** · 08-01-2017, 08:59 AM

Good morning. So I tried making the following edits but I keep getting an error saying Compile Error: Variable Not Defined with the word Cell being highlighted. Thoughts?

Please Login or Register  to view this content.

**Syrkrasi** · 08-01-2017, 10:22 AM

Change the following

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

As for the second (new) request, please open a new Thread

**deleter** · 08-01-2017, 10:26 AM

Hi. Thank you for the reply. I made the change as you suggested but I get the same error. Variable not defined with Cell being highlighted.

**popipipo** · 08-01-2017, 10:46 AM

Excel don't recognizes 1-1-1111 as a date.
The first date in excel is 1-1-1900

**deleter** · 08-01-2017, 10:49 AM

Originally Posted by popipipo

Excel don't recognizes 1-1-1111 as a date.
The first date in excel is 1-1-1900

Hi popipipo

I changed to as follows

Please Login or Register  to view this content.

But same issue

**Syrkrasi** · 08-01-2017, 11:28 AM

I am unable to replicate your error (and I used both of our codes.)
Pls attach a sample workbook which produce the error. thanks

**deleter** · 08-01-2017, 11:36 AM

Never mind. I simple selected the column, hit "Find and Replace" and replaced any blanks with the dummy date value. Thank you anyway for all your help!

Insert default date into blank cells in date column

LinkBack

Thread Tools

Rate This Thread

Display

Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Re: Insert default date into blank cells in date column

Thread Information

Users Browsing this Thread

Similar Threads

Help with COUNTIF date is in specified month/year but date column has blank cells

[SOLVED] If formula, if two cells are blank, return blank, if one has a date, subtract todays date

[SOLVED] count blank cells in one column based on date in different column

VBA code to insert blank rows in a column with dates, after every new date

macro to highlight blank cells in row if date in column A

Excel 2007 : Count blank cells in one column that also meet a date condition in another column

When I insert a new worksheet into my workbook, the cells default as long date?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions