+ Reply to Thread
Results 1 to 8 of 8

Formula to calculate days till a given date + more

  1. #1
    Registered User
    Join Date
    11-18-2008
    Location
    NY
    Posts
    4

    Formula to calculate days till a given date + more

    Looking for a formula to return number of months left from TODAY to a particular future date (09/30/2009). The value is then going to be multiplied by value in neghboring cell (e.g. B2) to produce a total amount to be charged from todays date till 09/30/2009.

    Trick is that when I use this formula tomorrow, it should not change the value of this total amount - it should stay same as it was today.

    ideas? Thanks

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578
    First question;
    What is a month? If today is the 25th of Jan and end date is March 3rd, is that 1, 2 or 3 months? Do you want a month to be 30 days or any month within the time period which is wholely represented?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Contributor EdMac's Avatar
    Join Date
    01-23-2006
    Location
    Exeter, UK
    MS-Off Ver
    2003
    Posts
    1,264
    Trick is that when I use this formula tomorrow, it should not change the value of this total amount - it should stay same as it was today.
    In that case you can't use the TODAY function as it is volatile and will update.

    Options are to either enter the date manually or use VBA to fix the date when a cell is selected.
    Ed
    _____________________________
    Always learning, but never enough!
    _____________________________

  4. #4
    Registered User
    Join Date
    11-18-2008
    Location
    NY
    Posts
    4
    Quote Originally Posted by EdMac View Post
    In that case you can't use the TODAY function as it is volatile and will update.

    Options are to either enter the date manually or use VBA to fix the date when a cell is selected.
    Thanks - I thought it was not possible.

    I could Copy|Paste end result as Special/Value - but that will give me a constant and nobody will ever know how I arrived to it.

    Will have to keep the number of months manual.

    Thanks for help.

  5. #5
    Registered User
    Join Date
    11-18-2008
    Location
    NY
    Posts
    4
    OK - how about we modify this a little... Need to count WORKDAYS from TODAY till a given date in the future. Know that NETWORKDAYS should do that trick - but can I include TODAY as part of the formula? some hand holding is appreciated....

  6. #6
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    199
    You can try the DATEDIFF formula that may work for you...you won't find help for it within Excel, but here is a link of the proper syntax.

    http://www.oraxcel.com/projects/litl.../datediff.html

    Hope that helps.

  7. #7
    Registered User
    Join Date
    11-18-2008
    Location
    NY
    Posts
    4
    Quote Originally Posted by Claymation View Post
    You can try the DATEDIFF formula that may work for you...you won't find help for it within Excel, but here is a link of the proper syntax.

    http://www.oraxcel.com/projects/litl.../datediff.html

    Hope that helps.
    OK - can not find this function in Excel - I do have all toolpaks installed.....

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    Quote Originally Posted by DmitriK View Post
    OK - how about we modify this a little... Need to count WORKDAYS from TODAY till a given date in the future. Know that NETWORKDAYS should do that trick - but can I include TODAY as part of the formula? some hand holding is appreciated....
    If you want workdays then, like you say, you need NETWORKDAYS function, not DATEDIFF.

    Try

    =NETWORKDAYS(TODAY(),A1)

    where A1 contains your future date. This counts all workdays in the range including the start date (Today) and the end date (A1), you can also exlude holidays. List the holiday dates in H1:H10, for instance and amend formula to

    =NETWORKDAYS(TODAY(),A1,H1:H10)

+ 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