Share via

Excel Formula 'If the date change then amount shift'

BravoClarisseG-7662 0 Reputation points
2026-02-05T11:19:13.7733333+00:00

What formula in excel that once the date manually change then number of due date change , the amount should fall in respective date according to range of days then 0 will appear on the column that the amount left

Moved from: Microsoft 365 and Office | Excel | Other | Windows

Microsoft 365 and Office | Excel | For education | Other

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2026-02-05T16:01:37.51+00:00

    Dear @BravoClarisseG-7662, 

    Good day! Welcome to Microsoft Q&A forum! 

    Based on your description, I understand that you’re looking for an Excel formula that automatically reallocates an amount to the appropriate due date when a date is manually changed. The amount should fall into a corresponding date range based on the number of days, and the original column should display 0 once the amount has shifted.

    Regarding this request where the number of days and the due date automatically change based on the date entered, you can create a formula that calculates the number of days left until the due date and shows 0 once the due date has passed.

    Assumptions:

    • Cell A1: Current date (this will be entered manually by the user).
    • Cell B1: Due date (this is the due date that is calculated based on a range of days).
    • Cell C1: Amount (this is the amount that will be shown based on the number of days left).
    • Range for the due date: The due date should be based on some number of days from the current date.

    So the steps to create the formula would be:

    • Calculate the number of days between the current date and the due date: The formula to calculate the number of days left between today's date (cell A1) and the due date (cell B1) would look like this:
    • Calculate the amount that should fall according to the number of days left: To adjust the amount based on the number of days remaining, you can use an IF statement. This will show the amount as long as the due date hasn't passed. If the due date has passed, it will show 0. Here is the formula for Cell C1 (Amount):
    • Where "Amount" represents the actual amount that you want to assign. Replace "Amount" with the actual amount or cell reference for the amount.
    • Adding dynamic changes based on the number of days: If you want the amount to change dynamically based on the range of days, you could use a more complex IF or SWITCH function. For example, you could set different amounts for different ranges of days remaining:
    • This formula will:
      • Show "Amount 1" if the due date is within 7 days from today.
      • Show "Amount 2" if the due date is within 14 days from today.
      • Show "Amount 3" if the due date is beyond 14 days.
      • Show 0 if the due date has passed.

    Here is an example scenario:

    • Cell A1: Enter today's date (e.g., 02/05/2026).
    • Cell B1: Due date (e.g., 02/10/2026).
    • Cell C1: Amount calculated based on the formula.

    The final steps would be:

    • Cell C1 will display different amounts based on how many days are left before the due date and will turn to 0 once the due date has passed.
    • Make sure A1 is updated manually, and B1 reflects the due date, and the formula in C1 automatically adjusts the amount.

    =IF(B1 >= A1, IF(B1 - A1 <= 7, "Amount 1", IF(B1 - A1 <= 14, "Amount 2", "Amount 3")), 0)

    =IF(B1 >= A1, "Amount", 0)

    =B1 - A1

    Does this match what you’re trying to achieve?

    If you’re able to share a bit more about your situation, I might be able to suggest some helpful next steps. 

    Please understand that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution.

    Kindly let me know when there are updates or if you need further assistance. Any updates you’re able to share would be really helpful. I appreciate your time and look forward to hearing how things are going! 

    Thank you for your time and patience.

    If you have extra questions about this answer, please click "Comment". 

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

  2. AI answer

    2026-02-05T11:19:22.3566667+00:00

    To achieve the functionality where the amount shifts according to the date change in Excel, you can use a combination of the IF, TODAY, and OFFSET functions. Here's a general approach to set this up:

    1. Assuming you have a table with dates in column A and amounts in column B.
    2. In column C, you can use a formula that checks if the date in column A has changed compared to today's date and adjusts the amount accordingly.

    Here’s an example formula you could use in cell C2:

    =IF(A2<TODAY(), 0, B2)

    This formula checks if the date in A2 is less than today’s date. If it is, it returns 0; otherwise, it returns the amount in B2.

    1. Drag the formula down to apply it to other cells in column C.

    You can adjust the formula based on your specific needs, such as changing the conditions or the range of dates. This method will help you dynamically shift amounts based on date changes.

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.