+ Reply to Thread
Results 1 to 7 of 7

Automatically generating a 40 day future date

  1. #1
    Registered User
    Join Date
    06-07-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Automatically generating a 40 day future date

    Hi all,

    Apologies if this is in the wrong forum. As part of a spreadsheet I am creating, I will be working within specific timeframes with a large volume of queries. For visibility, I have decided to give myself indication of what a deadline is approaching. Here is what I need excel 2007 to do;

    If I put a date in column A1, I need A2 to calculate the date as 40 calendar days into the future. Ideally, I would also like the box to be able to change colour dependant on where the query was up to. As a rough indicator, > 0 but < 14 green / > 14 but < 29 amber / > 29 onwards would be red. After the 40 day period, I suppose I would want it to just... go blank again, unless I had a 'completed' drop down in A3 which would overwrite everything and make it a lighter green. I know I wouldnt always want it to be a red colour after the 40 day time elapsed.

    Any experienced excel'ers have any input?

    Being new to excel 2007, I am unsure if this is at all feasible, but I thought I would ask. I also did a few forum searches on 'dates' and such but nothing matched my criteria. If there is a matching topic, please feel free to direct me.

    Many thanks for any help you can offer.

    Dan

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Automatically generating a 40 day future date

    Hi Dan and welcome to the forum.

    In A2, put this =A1+40

    For colors you need Conditional Formating using IF(AND function

    =if(and(a2>0,a2<=10) >>Green color...

    Same for the others

    Is this, works for you?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Automatically generating a 40 day future date

    =TODAY()+40 is 40 calendar days into the future from today.

    You use Conditional Formatting to change cell colours and fonts dependent on criteria.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    06-07-2012
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: Automatically generating a 40 day future date

    Woah. That was both quick and simple! I thought it would be a huge line of code. Thanks for the help with that guys.

    If I wanted to expand that rule to apply across B2 to B100, do I have to manually retype the formula or is there a way to copy and paste it? I did drag it down manually but it shows a date like 2/1/1900 in all of the empty B2 (onwards) fields. Ideally, I would want them to be blank until I put a date in A2 - A100.
    Last edited by enlight85; 06-08-2012 at 03:58 AM.

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Automatically generating a 40 day future date

    try ..


    =if(A1>1,A1+40,"") then drag down again..
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Automatically generating a 40 day future date

    You're welcome. Thanks for the rep.

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Automatically generating a 40 day future date

    Not sure that i understand your new request but try this in A2 and copy down.

    =IF(A1<>"",$A$1+40,"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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