Keeping date format when using cell reference

[lysander]

Hi

I have a array of raw data that includes multiple columns and rows of dates. These dates are actually not formatted as dates but are recognized by excel as text because they come from a third party reporting system.

In excel I use formulas to pull information from this array into another worksheet. However excel automatically changes the date into a number format in the destination cell. This is ok when there is a date in the cell I'm pulling from because I can date format as required. However it is an issue when there is no date in cell (i.e. the origin cell is blank (this is frequent)) because when I use the date format I get a date 01/01/01 instead of just a blank cell.

What can I do to avoid having a date 1/00/00 0:00 appear and just keep the cell blank if there is no date in the origin cell?

Thanks

Sample of raw data looks like this :

12/1/2017 0:00	12/1/2017 0:00
12/5/2017 0:00	12/5/2017 0:00
12/5/2017 7:45
12/6/2017 19:12
11/17/2017 0:00	11/17/2017 0:00

<colgroup><col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;" span="2"> <tbody> </tbody>

<colgroup><col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4790;" span="3"> <tbody> </tbody>

 

[Rick Rothstein]

Try having your formula test for blank cells, something like this...

=IF(A1="","",YourFormulaWithoutEqualSignGoesHere)

 

[lysander]

Hi Rick
I like the idea but because I'm pulling data using a vlookup formula I'm not sure how to nest the If function so that the if function is working based on what the vlookup has identified. For instance my vlookup is written like this:

=VLOOKUP($A2,WIP,2,FALSE)

Thanks for advising how I can first ***** if the cell is blank before returning the value or blank.

 

[lysander]

***** should read identify. not sure why it can out as stars

 

[lysander]

I got it. Using LEN is solution.
=IF(LEN(VLOOKUP($A2,IC_WIP!IC_WIP,9,FALSE))=0,"",VLOOKUP($A2,IC_WIP!IC_WIP,9,0))

Thanks