How to set a column to automatically add a date based on an admission date then number of weeks.

[Queenbee86]

Hello, 

I use excel to keep track of certain information regarding contracts, I have been able to format it to do everything I need except calculate the date based on an admission date and then the number of weeks.

For example we have an admission on 8/04/2024 and they are booked in for 2 weeks so the departure date would be 22/04/2024, I'm wanting this departure date to automatically calculate when the relevant information (admission date and weeks) is entered into the relevant columns. 

The only thing I have been able to find is how to have the date calculated by using the following formula =IF(A2,A2+(B2*7),"") after details are entered, however I'm wanting this to be preset in a sense so that it automatically adds the date, that way if someone else uses the spread sheet, after they add the admission date in correct cell then they add the weeks in its correct cell as soon as they hit tab or click out of the weeks cell it generates the right date.  I'm hoping this makes sense? 

I have been using Excel for web.

[#2 · Accepted solution]

@Queenbee86 

To achieve your desired functionality in Excel for the web, you can use a combination of formulas and data validation.

Here is a step-by-step guide:

1. Set up your columns:
• Column A: Admission Date
• Column B: Number of Weeks
• Column C: Departure Date (auto-calculated)
2. Enter your formula for the Departure Date:
• In cell C2, enter the formula =IF(AND(A2<>"",B2<>""), A2+(B2*7), ""). This formula checks if both the Admission Date and Number of Weeks are entered. If they are, it calculates the Departure Date; otherwise, it leaves the cell blank.
3. Set up data validation for the Number of Weeks column:
• Select the cells in column B where you will enter the number of weeks (e.g., B2:B100).
• Go to the Data tab on the ribbon.
• Click on Data Validation.
• Choose Whole Number in the Allow dropdown.
• In the Data box, select Between and enter 1 and the maximum number of weeks allowed.
• Click OK to apply the data validation.

With this setup, users can enter the Admission Date and Number of Weeks, and the Departure Date will automatically calculate when they either move to another cell or press Enter. Data validation ensures that only whole numbers are entered for the Number of Weeks.

This approach should work in Excel for the web, allowing for automatic calculation of the Departure Date based on the Admission Date and Number of Weeks.Formularbeginn The text and steps was created with the help of AI.

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@NikolinoDE 

Thank you for taking the time to give me a very easy to follow detailed run down. I will try it now and be sure to let you know how I go,