+ Reply to Thread
Results 1 to 6 of 6

Removing "0" Value from cells before applying Date Format

  1. #1
    Forum Contributor
    Join Date
    02-23-2007
    Location
    England
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20072) 64-bit
    Posts
    102

    Removing "0" Value from cells before applying Date Format

    Hello All

    Hopefully a simple one for you here (driving me nuts)!

    I have a spreadsheet which after perfroming a VLookup, has some cells showing "0". Now when I apply a 'date' format to the elevant columns, those cells which had '0' now show "00/01/1900". I have tried making only the cells "0" as blank cells, so that when I apply the date format, the blank cells remain blank and the other relevant dates format as expected. However, when I apply the code to remove only the cells containing "0", it actually removes all "0", so the good dates become messed up (i.e. 15/02/2014 becomes 15/2/214). I have tried all kinds of ways to do this automatically but failing big time.

    Here is the code I am using before before applying the date format:
    Please Login or Register  to view this content.
    I have attached a copy of the spreadsheet just for ease of use.

    Many thanks All
    G1
    Attached Files Attached Files
    Last edited by ghostly1; 02-17-2014 at 10:23 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Removing "0" Value from cells before applying Date Format

    I have looked at your spreadsheet and cannot find any date fields that are calculated with a Vlookup. To avoid the issues you talk about, however, I would wrap an IF statement around the Vlookup in this manner

    If(vlookup(your critieria)=0,"",vlookup(yourcriteria))

    This should solve your problem as I understand your issue.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Removing "0" Value from cells before applying Date Format

    Maybe:

    Change this:

    Please Login or Register  to view this content.
    To this:

    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    02-23-2007
    Location
    England
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20072) 64-bit
    Posts
    102

    Re: Removing "0" Value from cells before applying Date Format

    Hello Alan and John

    Thank you both so much for your expert advice. Firstly Alan I apologise for not listing the vlookups I have attached to my spreadsheet, for without those it would be impossible to fully understand what I was trying to do. Your code didn't work on my sheet as my embedded VB code is doing the vloopup via a FormulaArray, so wrapping in an 'If' statement was not allowed. However, luckily for me I dodged a bullet as John's simple replacement of the 'xlPart' to 'xlWhole' seems to have worked perfectly for me.

    So I would like to thank both of you for your time and quick responses, your help has been invaluable.

    Cheers guys
    G1

  5. #5
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Removing "0" Value from cells before applying Date Format

    You're welcome. Glad to help out and thanks for the feedback.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Removing "0" Value from cells before applying Date Format

    I, too, thank you for your feedback and the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 10-17-2013, 02:29 PM
  2. Replies: 3
    Last Post: 08-13-2013, 06:25 AM
  3. Date format of just "st" "nd" "rd" and "th" with text included
    By notrandom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-10-2013, 05:45 PM
  4. Applying a "Left" formula to an entire column with heading "ValueCol"
    By aad401 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2012, 04:14 PM
  5. Replies: 2
    Last Post: 08-21-2008, 06:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1