In Excel, you may add 12 weeks from today — or any number of weeks — to dates by using a formula to add the appropriate number of days.
Step 1: Create a new column to hold the new dates and choose the cell where the first new date should be displayed. To open the Function Arguments window, click the fx icon.
Step 2: Click the OK button after selecting Sum from the list of available functions.
Step 3: Select the first date from the spreadsheet by placing your cursor in the Number1 box.
Step 4: Click the OK button after entering the number of days you wish to add to the date in the Number2 column. Enter 84 to add 12 weeks to a date, for example (12 multiplied by 7).
Step 5: If necessary, apply the same formula to numerous dates by dragging the square icon (also known as the fill handle) from the bottom-right corner of the cell containing the formula you just constructed to other cells.
How to Use Excel to Calculate a Future Date (or Say, 12 weeks from today)
When dealing with Microsoft Excel, it’s common to perform calculations that use dates. When a bill is due or you plan to earn interest on an investment, you might want to compute a future date in Excel. To do math with dates and times, you can utilise Excel date functions.
Use the DateAdd function in Excel to add dates.
DateAdd is one of the most helpful date math functions in Microsoft Excel.
You can use DateAdd to add or subtract days, months, or years from a date that is hard-coded into your formula or in another cell. The method takes a text string that describes the interval type you’re dealing with, such as days or months, as well as the number of intervals you wish to add and the start date.
To subtract, use a negative number of intervals, and consult the Excel documentation for information on how to describe the interval type you’re working with. Days are denoted by the letter “d,” and months are denoted by the letter “m.”
The Excel date formula “=DateAdd(“d”, 5, “5-Jan-15″)” returns “10-Jan-15,” for example. When you add months to a date, they do not roll over to the next month, so one month after December 30 is January 30, and two months after that is February 28. (or February 29 in a leap year). You could use a reference to a cell, such as A5 or B6, in place of any of the strings, as you would in other Excel formulas.
Basic Date Arithmetic
You can utilise the basic arithmetic operations in a formula to simply add and subtract dates. If cell A5 holds the value “12/15/2017,” the formula “=A5+12” will add 12 days to the value in A5, resulting in 12/27/2017.
You can use these formulas to calculate dates by adding and subtracting days in general. This is useful if you need to locate a date that is a specific number of days or weeks before or after a specific date. To gaze backward, use a negative number of days.
The EDATE Function
EDATE is another important date math tool in Excel. It’s used to do month-level math, such as calculating the day of the year on the same numeric day a particular number of months in advance.
“EDATE(A5, 5)” will produce the date five months in the future, or 6/1/2017, if cell A5 contains the date 1/1/2017. The value will be returned as a “serial date,” which informs you how many days have passed since January 1st, 1900. The date will be displayed in a familiar format if the cell containing the result is formatted as a date. Negative numbers can be used to find dates that occurred before the chosen date.
In MS Excel, how do you add days to a date in a spreadsheet?
A spreadsheet in Excel is a document that is used to keep track of finances, contacts, and other numerical data. The user frequently adds a date when keeping track of data. To add or subtract days, months, or years, Excel includes data operations and formulas. The “Day()” function is one of these functions, and it is used to calculate the day in a given date.
Step 1: Open the Excel spreadsheet you wish to alter in the first step. You may also test the date formulas by creating a new spreadsheet. A new spreadsheet is produced automatically when you start Excel from the Windows Start menu.
Step 2: In the first cell labelled “A1,” type a date. Even if it’s the last day of the month or year, this can be any day of the year. Excel automatically transitions the date to the next month or year when you add a day to the date.
Step 3: To add to the days, use the “Day()” formula. In a cell adjacent to the A1 cell, type the new formula. The code below adds 10 days to the date in cell A1:
=date(year(a1), month(a1), day(a1) + 10)
The equal sign indicates that a formula has been input, and Excel requires it. The “date()” function creates a full date string from the year, day, and month.
Step 4: Take a look at the formula’s outcomes. In the A1 cell, you’ll notice that the date has been extended by 10 days. Furthermore, if the date in the A1 cell is changed, the formula updates and reflects the changes.
How to Format Dates in Microsoft Excel in Descending Order
When you sort a column of dates in Excel 2013, the sorting feature automatically analyses data based on cell formatting, so in most situations, Excel sorts by chronological order rather than alphabetically. If you’re working with a data set that has plain text in the cells, you’ll need to transform the text to a date format that Excel can comprehend first.
Sorting Dates in a Chronological Order
Select the column (or portion of a column) containing the dates you want to sort, then click the AZ or ZA sorting button on the Data tab. When working with dates, these buttons switch from alphabetical to chronological sorting. The AZ button sorts from oldest to newest, whereas the ZA button sorts from most recent to most recent.
Reformat Text as Dates
You have your cells set to text formatting if you try to sort dates and Excel sorts them alphabetically instead. However, unless you retype each of your dates, changing to date formatting will not address the problem. If you have a bigger list, the DATEVALUE method is a better option.
Step 1: Write a DATEVALUE Formula
In the same row as your initial date, select an empty cell. Start a formula with =DATEVALUE(and then click the first date in your list to insert it into the appropriate field. To finish the formula, press Enter.
Step 2: Fill the Remaining Cells
Drag the fill handle (the small square in the lower-right corner of the cell) downward to cover as many rows as you have dates in the selected cell.
Step 3: Change Cell Formats
Select all of the cells with date serial numbers, then go to the Home tab and open the Number section’s drop-down menu. To reformat the cells as dates, select either Short Date or Long Date. Sorting these cells now works as it should.
You may also be interested in :
