Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 44
  • Last Modified:

Trying to make a formula blank.

If the following Ln on a sheet, grabs a value and that value is a Date.

ActiveWorkbook.Names.Add Name:="ColI", RefersToR1C1:="=" & Me.Name & "!R" & Target.Row & "C9" ' LnReqDelDate

however if the cell it is grabbing in column 9 from, is blank it returns:  1900-01-00

I use the formula on =ColI on another sheet to put that value in.
Is there another way to write the formula  =ColI so that if it returns 1900-01-00 (meaning blank) , it can make it ""     ??
0
RWayneH
Asked:
RWayneH
  • 5
  • 4
1 Solution
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Custom format the formula cell with dd/mm/yyyy;; to hide 1900-01-00.
0
 
RWayneHAuthor Commented:
I cannot do that.. the required format is yyyy-mm-dd  because if it is not blank it needs to stay and use the date and in that specific reformat.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's not the beg deal, you can custom format it with yyyy-mm-dd;;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
RWayneHAuthor Commented:
it is already....  I need it to be blank without the 1900-01-00 showing   ""
0
 
RWayneHAuthor Commented:
any other value is acceptable > today.  I am having issue making the cell = ""
0
 
RWayneHAuthor Commented:
ohhhh  hello the suffix of ;;  that worked.  I feel tiny.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay I made them bold so that you don't miss the complete syntax.
Glad it worked at last for you.
0
 
RWayneHAuthor Commented:
Thanks
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome. Glad to help.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now