Search code examples
excelif-statementexcel-formuladate-range

How do I create a formula for "if value in one cell is 1 then multiply by 1 and so on?

I am a beginner at excel. I want to multiply a cell with a number as the input given in another cell.

  1. Cell A1 has the Input that is the duration(week/s) which is 1/2/3/4...
  2. Cell A2 has the Start Date(i.e. Monday)
  3. Cell A3 has the End Date(which is supposed to be Friday) which I want to calculate as per the number of weeks I enter in the Cell A1 Example: If the Start Date(A2) is 14-02-2022 and the Duration(A1) is 1 week, the End Date(A3) should be 18-02-2022.

I tried with =A1*IF(A2=1,1.0001, IF(A2=2,1.00026)), to which I got the results. But when I tried to add the formula for the further durations, it said that I have entered too many arguments for this function.

How can I write a formula to handle the multiple durations?

Solution

  • enter image description here

    Your start date can be any weekday (Monday-Sunday) and the formula will return always a Friday. Notice how rows 6:9 return same results even if the start date are not Mondays.

    Formula in column C:

    =7*(A2-1)+(B2+(5-WEEKDAY(B2;2)))

    Please, notice I'm using an european WEEKDAY setup, that means that first day of week is Monday (in other countries is Sunday). If you change this, you'll need to adjust the whole formula:

    WEEKDAY Function