+ Reply to Thread
Results 1 to 15 of 15

List of people that I want to calculate how many days they have left until End date

  1. #1
    Registered User
    Join Date
    02-09-2022
    Location
    Reykjavik
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    35

    Smile List of people that I want to calculate how many days they have left until End date

    Hi Hi
    I have a problem that I canīt seem to find out how to solve. I have a list of people that I want to calculate how many days they have left until End date, can you help?
    I have attached file that I think will explain it.
    Attached Files Attached Files
    Last edited by AliGW; 02-09-2022 at 05:57 AM. Reason: Title changed - please think more carefully about your thread titles in future!

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: List of people that I want to calculate how many days they have left until End date

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. As you are new here, I have done it for you this time.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: List of people that I want to calculate how many days they have left until End date

    Try this:

    =MAX(MIN(IF(OR(D3="",AND(D3<=Calcul_9,D3-Calcul_9>30)),30,-((Calcul_9-30)-D3)),30),0)

    You may need to replace commas with semi-colons for your locale.

  4. #4
    Registered User
    Join Date
    02-09-2022
    Location
    Reykjavik
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    35

    Re: List of people that I want to calculate how many days they have left until End date

    Thank you so much for that <3

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: List of people that I want to calculate how many days they have left until End date

    See post #3 for a suggested solution.

  6. #6
    Registered User
    Join Date
    02-09-2022
    Location
    Reykjavik
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    35

    Re: List of people that I want to calculate how many days they have left until End date

    Regarding post #3
    This formula gives me zero in all cells
    =MAX(MIN(IF(OR(D3="",AND(D3<=Calcul_9,D3-Calcul_9>30)),30,-((Calcul_9-30)-D3)),30),0)

    I have changed commas with semi-colons so I don't think this is working

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: List of people that I want to calculate how many days they have left until End date

    Attach a fresh workbook showing the issue. It works for me on your data.

    AliGW on MS365 Insider (Windows) 64 bit

    F
    G
    1
    Calculation date
    2
    30/09/2021
    AliGW
    3
    30
    30
    4
    30
    0
    5
    FALSE
    30
    6
    30
    30
    7
    2
    2
    8
    9
    10
    31/10/2021
    AliGW
    11
    30
    30
    12
    30
    0
    13
    30
    30
    14
    30
    30
    15
    30
    2
    16
    17
    18
    30/11/2021
    AliGW
    19
    30
    30
    20
    30
    0
    21
    0
    30
    22
    30
    30
    23
    30
    2
    Sheet: English
    Attached Files Attached Files
    Last edited by AliGW; 02-09-2022 at 06:29 AM.

  8. #8
    Registered User
    Join Date
    02-09-2022
    Location
    Reykjavik
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    35

    Re: List of people that I want to calculate how many days they have left until End date

    WOW this works thank you so much
    I have been sitting over this for days
    Thank you so much you are awesome

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: List of people that I want to calculate how many days they have left until End date

    Pl see file. Formulas are given in H column.

    Mod Edit: =IF(MAX(0,IF($D3="",Calcul_9,MIN(Calcul_9,$D3))-MAX(EOMONTH(Calcul_9,-1),$C3))>30,30,MAX(0,IF($D3="",Calcul_9,MIN(Calcul_9,$D3))-MAX(EOMONTH(Calcul_9,-1),$C3)))
    Attached Files Attached Files
    Last edited by AliGW; 02-09-2022 at 06:58 AM. Reason: Added formula for the benefit of all.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: List of people that I want to calculate how many days they have left until End date

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

  11. #11
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: List of people that I want to calculate how many days they have left until End date

    In H3
    Please Login or Register  to view this content.
    In H11
    Please Login or Register  to view this content.
    In H19
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    02-09-2022
    Location
    Reykjavik
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    35

    Re: List of people that I want to calculate how many days they have left until End date

    Hi Hi
    what is the significant in the Min and Max in this formula?
    =MAX(MIN(IF(OR(D3="",AND(D3<=Calcul_9,D3-Calcul_9>30)),30,-((Calcul_9-30)-D3)),30),0)

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: List of people that I want to calculate how many days they have left until End date

    Your answers cannot be over 30, you said, so here's your base formula:

    IF(OR(D3="",AND(D3<=Calcul_9,D3-Calcul_9>30)),30,-((Calcul_9-30)-D3))

    which will return values over 30, so we add this:

    MAX(...,30)

    to limit them.

    The base formula will also return negative values that need changing to zero, so we add this:

    MIN(...,0)

    Hope this helps. Use Evaluate Formula on the Formulas ribbon to get further insights.

  14. #14
    Registered User
    Join Date
    02-09-2022
    Location
    Reykjavik
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    35

    Re: List of people that I want to calculate how many days they have left until End date

    Thank you, the formula did it's job I just wanted to understand it to use it in my other work. Yes I will check out Evaluate Formula
    Thanks again

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: List of people that I want to calculate how many days they have left until End date

    Yes, of course - always wise to do this. Glad to have helped.

+ 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] the way we solve Solve equation. Can Excel solve this?
    By tta.akmal in forum Excel General
    Replies: 3
    Last Post: 08-10-2020, 07:49 AM
  2. [SOLVED] Solve for x
    By bbarth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-01-2015, 10:28 AM
  3. Solve
    By abhijit_an in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-21-2007, 04:52 AM
  4. i need to solve this please!
    By freakzilla in forum Excel General
    Replies: 3
    Last Post: 10-26-2006, 09:10 AM
  5. Help me to solve this….
    By nesh in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 10-07-2006, 05:28 AM
  6. how to solve this?
    By jackoat in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-28-2006, 08:11 PM
  7. [SOLVED] Solve IF value
    By pdberger in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 09:05 AM

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