+ Reply to Thread
Results 1 to 8 of 8

+2 date formula adds 2 days to blank cells

  1. #1
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    +2 date formula adds 2 days to blank cells

    I wrote a quick import formula (=A2)+2 to pull in the date from A2 and add 2 days. If there is a date present it works fine. If A2 happens to be empty or blank it returns 1/2. Apparently if a cell is blank and formatted for dates (mm/dd) it has a value of 1/0 because something like 4/6 will return 4/8. How can I keep this from happening? Both columns are formatted for dates, and I have special formatting on worksheet to replace all zeros in cells with blank cell.

    A/2: blank
    S/2: =(A2)+2
    A format for dates
    S format for dates
    worksheet formatted to show blank cells for zero cells
    S2: populates 1/2

    A3: "4/7" (april seven)
    S3: =(A2)+2
    A format for dates
    S format for dates
    worksheet formatted to show blank cells for zero cells
    S2: populates "4/9"

    I want S2 to populate nothing. Can I use IF or SUMIF?

    Any help is greatly appreciated

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: +2 date formula adds 2 days to blank cells

    =if(a2="","",a2+2)
    Cheers!
    Deep Dave

  3. #3
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: +2 date formula adds 2 days to blank cells

    That formula I had figured out. The problem is that it returns 1/2...the same thing as a simple grab. What I need is a formula that grabs the date from B2 only if there is a date there and returns nothing if B2 is blank (A2 in your formula is B2 to me).

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,879

    Re: +2 date formula adds 2 days to blank cells

    That's exactly what the formula is doing right?

  5. #5
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: +2 date formula adds 2 days to blank cells

    Desensredd.xlsm

    Unless I do not know how to use it, no. Here is my book. click on teh dawn tab and drop yoru formula into P2 then drag it down. You will see every date will have 2 days added to it, but the blank cells will return 1/2. I think it has something to do with number formating...I had a dilly of a time today with that trying to make it so the nurses could enter 2245 and have it return 22:45. I know that is time and has nothing to do with date, but I swear it never did this before.

  6. #6
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel Online; 365
    Posts
    633

    Re: +2 date formula adds 2 days to blank cells

    there are hidden reference in column b of your data..

    This formula should work:

    =IF(B2>0,(b2)+2,"")

    Assuming that column C is empty whilst column B is empty:

    =IF(OR(B2="",C2=""),"",(b2)+2)
    If I've helped U pls click on d *Add Reputation

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: +2 date formula adds 2 days to blank cells

    Ths formula suggested post #2 would work for you, you just needed to adjust the references. The problem was that you said A2 (acutally B2) was blank, but it is not, it contains a formula that returns zero.

    =IF(B2=0,"",B2+2) should work for you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Forum Contributor
    Join Date
    08-28-2013
    Location
    grand rapids, michigan
    MS-Off Ver
    Excel 2010
    Posts
    163

    Re: +2 date formula adds 2 days to blank cells

    Thank you!

+ 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. [SOLVED] Formula to calculate number of days & ignore blank cells
    By Mifty in forum Excel General
    Replies: 11
    Last Post: 06-06-2021, 01:42 AM
  2. [SOLVED] If formula, if two cells are blank, return blank, if one has a date, subtract todays date
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 12:47 PM
  3. [SOLVED] Macro adds number to entire selection, how to avoid adding to blank cells?
    By Butcher1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-26-2014, 03:05 PM
  4. Replies: 19
    Last Post: 03-04-2013, 01:16 PM
  5. [SOLVED] Date formula that adds 7 days to a cell when sheet is copied
    By dkent in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2006, 11:10 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