+ Reply to Thread
Results 1 to 6 of 6

formula which tells me when something is due in days

  1. #1
    Registered User
    Join Date
    01-16-2015
    Location
    Birmingham, England
    MS-Off Ver
    excel 2010
    Posts
    4

    formula which tells me when something is due in days

    Hi I'm not the best at formulas I know what I want but haven't a clue how to get it, tried looking at the help section and not sure if its a EDATE, IF or countif function I use.

    what I have is a column that tells me when a licence is due for renewal, the person has a 4 month window to renew the licence I would like a column that tells me in days and that counts down how long they have to renew and when it gets to a certain length of time it changes colour to prompt me to act on this, can anyone help!!!

    Hope I've explained what I want ok.

    Sally

  2. #2
    Forum Contributor
    Join Date
    09-07-2013
    Location
    chennai
    MS-Off Ver
    Excel 2010
    Posts
    114

    Re: formula which tells me when something is due in days

    Hi , Please attach your file,

  3. #3
    Registered User
    Join Date
    01-16-2015
    Location
    Birmingham, England
    MS-Off Ver
    excel 2010
    Posts
    4

    Re: formula which tells me when something is due in days

    Can this forum actually help or is it a forum that you have to pay to get a response

  4. #4
    Registered User
    Join Date
    01-16-2015
    Location
    Birmingham, England
    MS-Off Ver
    excel 2010
    Posts
    4

    Re: formula which tells me when something is due in days

    Thank you so much this is the sheet I'm working on
    Attached Files Attached Files

  5. #5
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: formula which tells me when something is due in days

    welcome to the forum, sally. assuming licence due date in A2, to get the date 4 months later would be:
    =EDATE(A2,4)
    so if A2 is 29 Dec 2014, you would get 29 Apr 2015

    to count how many days is left to that day from today would be:
    =EDATE(A2,4)-TODAY()
    you would then get 103 days if today is 16 Jan 2015.

    if you have a case where due date + 4 months is before today's date, and you want to show a text like "expired", then:
    =IF(EDATE(A2,4)-TODAY()<0,"Expired",EDATE(A2,4)-TODAY())

    for a certain length of time, say <30 days, try this:
    select the range you want to apply to (say from A2:B20)
    go to Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format -> Format values where this formula is true:
    =EDATE($A2,4)-TODAY()<30
    format colour

    ps: havent seen your file while i was replying
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  6. #6
    Registered User
    Join Date
    01-16-2015
    Location
    Birmingham, England
    MS-Off Ver
    excel 2010
    Posts
    4

    Re: formula which tells me when something is due in days

    wow thank you so much works brilliantly you are a star.

+ 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] Excel formula that tells how much month left to reach target savings.
    By fadu4u in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-27-2014, 01:59 PM
  2. Replies: 0
    Last Post: 05-21-2014, 03:30 PM
  3. A formula that tells me if an account is active based on data in a table
    By mfairhurst1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-13-2013, 03:31 PM
  4. [SOLVED] Formula that tells me whether a date is later than the current.
    By VivatMartin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-20-2012, 10:20 AM
  5. formula..that tells me if any given cell has "510"
    By Chrisnelsonusa in forum Excel General
    Replies: 4
    Last Post: 05-01-2009, 05:35 PM

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