+ Reply to Thread
Results 1 to 17 of 17

Date expirations

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Date expirations

    What should I do for these? pls help.

    cell a is the date of expiration
    cell b is the "status", wherein it will automatically says "expired" or "ongoing".

    example:

    cell a1: December 1 2012

    what will i do for the cell b1 to automatically says "expired" or "ongoing"?

  2. #2
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Date expirations

    Hi

    Put in the formula =if(today()>A1,"Expired","Ongoing") into the B1 cell. If you require you can also use conditional formatting to turn the B col red for expired.

    Chris
    Last edited by dogberry; 11-07-2012 at 02:31 AM.
    Click my star if I helped Thanks

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Date expirations

    Hi thank you so much!

    What if iwant it with color, red for expired and green for ongoing? Thank you thank you!

  4. #4
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Re: Date expirations

    Hi

    Go into Conditional Formatting select managw rules then format cells that contain and change the pulldown to specific text follow the prompts and do this twice once for red and once for green.

    Hope that helps

    Chris

  5. #5
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Re: Date expirations

    Hi

    I have uploaded a file for you, have a look at conditional formatting rules manager and it shows you how it was done.


    Chris
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Date expirations

    Yehey!

    Thank you very much! You have a big heart!

    Gracie

  7. #7
    Forum Contributor dogberry's Avatar
    Join Date
    07-15-2012
    Location
    Wales, UK
    MS-Off Ver
    Excel 2010
    Posts
    624

    Re: Date expirations

    Hi

    Pleased to help dont forget to mark your thread as solved and thank you for clicking my star.

    Good luck

    Chris

  8. #8
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Date expirations

    how do i do that? sorry this is my first time here. thank You!

  9. #9
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Date expirations

    i still cant get it im sorry, i tried working on something else. Can you give me a step by step procedure on how you did it? Im sorry

  10. #10
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Date expirations

    Can anyone help me by giving me a step by step procedure on date expirations with color.

    a1 = Nov 12, 2012 B1 = this has to show if the cell a1 will expire in 30 days, red with a word expired for expired and green with a word renewed if still not expired. I will be very thankful if anyone can show me. Pls

  11. #11
    Forum Contributor
    Join Date
    09-27-2012
    Location
    London, England
    MS-Off Ver
    2003, 2010
    Posts
    344

    Re: Date expirations

    Hi - When you say "...cell a1 will expire in 30 days", do you mean 30 days from the date in cell A1?
    If solved kindly remember to mark Thread as solved.
    Click the small star icon at the bottom left of my post if this was useful.

  12. #12
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Date expirations

    Hi Gracieline,

    Try using below formula in cell B1

    =IF(AND(A1-TODAY()<=30,A1-TODAY()>=0),"Renewed",IF(A1-TODAY()<0,"Expired",""))

    And for green / red color.. see the the conditional formatting logics. press (alt + o +d) to bring the conditional formatting logic. thanks

    Date expirations.xlsx

    regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  13. #13
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Date expirations

    Thank you so much guys, Im sorry if I did post it twice, Ill be more careful next time.

    I am lost with conditional formatting, can anyone give me a step by step procedure on how to do it using conditional formatting?
    Cell a1 = Nov 8 2012
    Cell B1 = will say expired(filled with red color) or Renewed (filled with geen color). I mean the cell b1 will tell the status of cell a1. Pls help me in details. Big thanks!

  14. #14
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Date expirations

    Pls anyone answer me in details

  15. #15
    Registered User
    Join Date
    11-07-2012
    Location
    UAE
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Date expirations

    Thank you so much guys, Im sorry if I did post it twice, Ill be more careful next time.

    I am lost with conditional formatting, can anyone give me a step by step procedure on how to do it using conditional formatting?
    Cell a1 = Nov 8 2012
    Cell B1 = will say expired(filled with red color) or Renewed (filled with geen color). I mean the cell b1 will tell the status of cell a1. Pls help me in details. Big thanks!

  16. #16
    Forum Moderator 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: Date expirations

    @ Gracieline

    Nice that you found the solution in your problem. Nothing personal at all. Just forum rules!

    But....

    Moderators.

    REALLY, i don't like at all to delete my posts, specially when is a warning for dublicate post that_as i know_it's against the forum rules

    So.pls, DON'T DO this again.
    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.

  17. #17
    Registered User
    Join Date
    08-23-2012
    Location
    Houston, Texas
    MS-Off Ver
    Excel 2010
    Posts
    57

    Re: Date expirations

    This may help you out:

    Conditional Formatting with Dates
    A macro does what you tell it, not what you want.

+ 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