+ Reply to Thread
Results 1 to 3 of 3

Difficulties with repeating dates and numbers when extending column with formula

  1. #1
    Registered User
    Join Date
    10-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Question Difficulties with repeating dates and numbers when extending column with formula

    Hello Forum,

    I have worked with my excel book for a while now, and I am close to a happy point but just can’t iron out a few last bugs. If you could please help, I would much appreciate it! I am using Excel 2010.

    Problem:
    • I have attached my excel book for reference.
    • Column G & H = Due to the formula I have in the two columns (=NOW()-C22) & (C22+21) [C22 contains a date], when I drag it down to the rest of the rows which don’t have data yet, I get this in column G=41563 and this in H=1/21/1900. I would like to not be able to see anything there until I add subsequent data. <-- please let me know if I need to further elaborate : )

    If you see any other improvements you would suggest in my excel workbook (conditional formatting, etc), feel free to do so. Suggestions welcomed.

    Thank you for looking at my situation!

    Cheers,

    Atrios
    Attached Files Attached Files
    Last edited by AtriosM3; 10-16-2013 at 01:13 PM.

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,639

    Re: Difficulties with repeating dates and numbers when extending column with formula

    You need to put your formula like below..

    in cell G3
    Please Login or Register  to view this content.
    in cell H3
    Please Login or Register  to view this content.
    And then copy it down.

    I hope it helps.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,244

    Re: Difficulties with repeating dates and numbers when extending column with formula

    As a suggestion, if this is going to grow to any size, it might be to your benefit if you put the NOW() in its own cell and then reference in the formula. The reason for saying that is because NOW() and TODAY() (among others) are volatile functions that update with every change to the workbook. If you have a lot of them, it will tend to slow things down.

    Also, if you look at your 1st answer, it is giving 2, but 10/16/2013 - 10/15/2013 is 1? I would suggest using =today() rather than NOW()...NOW() includes the time as well, and it doesnt look like you need that?
    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

+ 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] Count number of days from column of repeating dates
    By Edwardbigfoot in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-28-2013, 04:22 PM
  2. Replies: 3
    Last Post: 11-22-2012, 11:19 PM
  3. Max from column of repeating numbers
    By RIKIL in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-27-2008, 11:26 AM
  4. [SOLVED] Repeating a sequence of numbers down a column
    By Nexan in forum Excel General
    Replies: 2
    Last Post: 03-04-2005, 02:06 PM
  5. [SOLVED] Trying to create repeating dates and then sort column by date
    By lizabright in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-10-2005, 09:06 PM

Tags for this Thread

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