Inserting date where there is none and converting existing ones

  • Thread starter Thread starter FrankBooth
  • Start date Start date
F

FrankBooth

Hello,

I want to iterate through a column of dates and where the cell is
blank I want to insert a preset date and where there is a date I want
to convert it to a certain format.

I've tried this formula for the first part: =IF("","20030101") but it
doesn't work.

So for example:
A blank cell would get 20030101 inserted and a cell with 7/15/2003
would get 20030715.

I'd like to do this with a formula if possible.

Any help on this would be appreciated.

Thanks in advance,
--FB
 
Frank,

Bit of tangled solution here.

If the cell contains "", it cannot contain your formula. As soon as you
enter the formula, any previous value is over-written. If you want to create
a value based upon a value in a cell, it has to be in another cell, and
refer back to the cell.

For instance
=IF(A1="","20030101","") in B1
 
Frank,

In a blank column insert this formula. Change the cell reference to meet
your needs.

=IF(ISBLANK(H20)=TRUE,"20030101",YEAR(H20)&TEXT(MONTH(H20),"00")&TEXT(DAY(H2
0),"00"))

PC
 
Why the need for a formula? - Is that just in preference to code?

Assuming it was, then the simplest way I can think of is to select the entire range of dates and
format them as custom yyyymmdd. Then with the range still selected, do Edit / Go To / Special /
Blank cells, type in the date and then hit CTRL+ENTER which will insert that date into every blank
cell.
 
Back
Top