Keeping date format when using cell reference

lysander

New Member
Joined
Dec 5, 2017
Messages
15
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:0012/1/2017 0:00
12/5/2017 0:0012/5/2017 0:00
12/5/2017 7:45
12/6/2017 19:12
11/17/2017 0:0011/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>
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,722
Office Version
  1. 2010
Platform
  1. Windows
Try having your formula test for blank cells, something like this...

=IF(A1="","",YourFormulaWithoutEqualSignGoesHere)
 
Last edited:

lysander

New Member
Joined
Dec 5, 2017
Messages
15
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

New Member
Joined
Dec 5, 2017
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,511
Messages
5,602,079
Members
414,500
Latest member
kevdragon1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top