= DATE(YEAR(日期) + 年數, MONTH(日期) + 月數, DAY(日期) + 天數) 1. 選擇您要放置計算結果的空白單元格,輸入公式 =DATE(YEAR(A2)+3,MONTH(A2)+5,DAY(A2)+15),然後按 Enter 鍵。請參見截圖: 注意:若要同時減去年、月和日的組合,請使用此公式 =DATE(YEAR(A2)-3,MONTH(A2)
To subtract a year from a date, we can use the same formula, but instead of positive, you should use the negative value for years. Another way to subtract a year is to type the -N of years in the separate cell, C2. In that case, you’ll use the =DATE(YEAR(A2)+$C$2,MONTH(A...
Note: To subtract a number of weeks from the date, you just need to change the number to negative in theNumbertextbox ofFormula Helperdialog, such as -12. Tips: 1. To find the exact formula quickly, you can also check theFiltercheckbox, and then type the key word of the f...
=Date2 – Date1 For example, to subtract the date in cell B3 from the date in cell A3, apply the formula below in cellC3, and press theEnterkey to get the result. =B3–A3 Tip: This formula would only work with dates in a valid format. For instance, attempting to subtract ...
=SWITCH(WEEKDAY(D5),1,”Sun”,2,”Mon”,3,”Tue”,4,”Wed”,5,”Thu”,6,”Fri”,7,”Sat”) Press ENTER and drag down the Fill Handle. This is the output. How to Extract Month and Year from Date in Excel You can extract month and year from the date by using the Format ...
=DATE(YEAR(C5),MONTH(C5)+D5,DAY(C5)) Use the following formula to subtract months from the Order Date in column C: =DATE(YEAR(C5),MONTH(C5-D5,DAY(C5)) Method 4 – Subtract and Add Years to Date in Excel Here’s the generic formula: =DATE(YEAR(date) + X years, MONTH(date...
Excel treats months as values (1 through 12), which makes it easy to subtract an earlier date from another date as long as both dates fall in the same year. Use the MONTH function to obtain the month value for each date, and then calculate the difference between the values. Select cell...
Add or subtract a number of days, months, or years to or from a date in a project schedule by using a simple formula, or you can use a sheet function that is designed to work specifically with dates.
Bonus: If you want to add or subtract years instead of months, set up your sheet, columns, and formatting the same as described above. Then, use the following DATE function in the new dates column with your correct cell references. =DATE(YEAR(B2)+C2,MONTH(B2),DAY(B2)) Another handy ...
D6 is the cell where you wrote the date, the minus sign (-) before today tells Excel to subtract the date in the future (D6) from today’s date. To ensure that your answer is a number with no decimal points, you can format and useNumberand setDecimalplaces to0. ...