提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中添加或减去日期,月份和年份?

当我们需要将Excel数据的CSV文件发送给其他用户或在其他程序中导入Excel数据的CSV文件时,有时我们需要将工作簿的数据导出为CSV文件。 在这里,我们提供了几种将工作表数据导出并保存为CSV文件的方法。 视频演示

  1. 使用公式添加或减去日期
  2. 在没有公式的情况下将天数加到或减去多个日期
  3. 在Excel中添加或减去几周到目前为止
  4. 在Excel中添加或减去迄今为止的月份
  5. 在Excel中添加或减去迄今为止的年份
  6. 在Excel中添加或减去年,月和日的组合
  7. 使用Kutools for Excel添加或减去迄今为止的天,周,月或年

在Excel中轻松添加/减去日期,周,月或年

在Excel中记住长时间复杂的公式很困难和乏味? Kutools for Excel's 配方助手 列出了最常用的公式,以帮助您快速计算和解决Excel中的问题 添加年/月/周/日期, 添加到目前为止的小时/分钟/秒, 总和绝对值, 找到最常见的价值等等。 全功能免费试用60天!
广告添加天数周数年至今

Kutools for Excel - 包括多个用于Excel的300便捷工具。 全功能免费试用60天,无需信用卡! 现在加入

使用公式添加或减去日期

您可能已经注意到日期是Excel中的5位数字。 因此,您可以像添加或减去Excel中的天数一样简单地添加或减去天数。

= 日期 + 天数

1。 选择要放置计算结果的空白单元格,键入公式 = A2 + 10,然后按 输入 键。
注意:要从日期中减去10天数,请使用此公式 = A2-10.

2。 如果需要将此公式应用于其他单元格,请根据需要将公式单元格的自动填充手柄拖动到这些单元格中。

现在,您已批量添加或减去这些日期的相同天数。 看截图:

在没有公式的情况下将天数加到或减去多个日期

与公式相比,某些Excel用户更喜欢直接添加或减去天数而不使用公式。 在这里,我将引导您使用Excel中的“选择性粘贴”功能在多个日期中添加或减去相同的天数。

1。 在10中输入您将在空白单元格中添加或减去日期的天数,然后复制它。 看截图:

2。 选择要添加或减去天数的日期,右键单击,然后选择 选择性粘贴 > 选择性粘贴 在上下文菜单中。 看截图:

3。 在“选择性粘贴”对话框中,请检查 添加 or 减去 根据需要选择,然后单击 OK 按钮。 看截图:

4。 现在,该数字被添加或减去日期,日期显示为5位数字。 保持选中这些5位数字,然后单击 主页 > 数字格式 框> 短日期 将它们再次转换为日期。

现在,您将看到在没有公式的情况下,在指定的日期范围内添加或减去指定的天数。 看截图:

你想现在从人群中脱颖而出吗? 30 +日期功能提升您的专业知识!

借助Kutools for Excel的30 +日期功能,您将获得有关3分钟日期的实用技能,并且比其他人更快更好地工作,轻松获得加薪和促销!

更可爱

有效地处理Excel中的日期问题,帮助您轻松获得工作中的其他人的欣赏。

照顾好你的家人

在Excel中与重复和琐碎的日期工作说再见,节省更多时间陪伴您的家人。

享受健康的生活

批量插入,修改或计算日期,每天减少数百次点击,告别鼠标手。

从不担心裁员

提高91%工作效率,解决Excel中的95%日期问题,提前完成工作。

释放你的记忆

关于日期的12 Kutools公式,停止记忆痛苦的公式和VBA代码,轻松工作。

Kutools for Excel 为300工作场景带来1500方便的工具,只需$ 39.0,但价值超过$ 4000.0 Excel的其他人培训,为您节省每一分钱!

在Excel中添加或减去几周到目前为止

您还可以应用公式来从Excel中的日期添加或减去指定的周数。 请做如下:

= 日期 + 7 * 周数

1。 选择要放置计算结果的空白单元格,键入公式 = A2 + 4 * 7,然后按 输入 键。
注意:要从日期中减去4周数,请使用此公式 = A2-4 * 7.

Kutools for Excel

在人群中脱颖而出

300 Handy Tools
解决Excel中的80%问题
现在免费试用

跟可怕的VBA和公式说再见!

2。 如果您需要在其他日期添加或减去周数,请根据需要拖动公式单元格的自动填充处理。

现在,您已批量添加或减去多个日期的相同周数。 看截图:

在Excel中添加或减去迄今为止的月份

对于添加或减去日期的月份,不适合在日期中添加或减去30,因为月份包含一年中不同的天数,一些包含30天,一些包含31天,而一些包含28天或29天。 因此,我们需要应用EDATE函数来处理这种情况。

= EDATE(日期, 几个月)

1。 选择要放置计算结果的空白单元格,键入公式 = EDATE(A2,3),并拖动此公式单元格的自动填充句柄,以根据需要将此公式应用于其他单元格。
注意:要从日期中减去3个月,请使用此公式 = EDATE(A2,-3).

2。 如您所见,EDATE函数将返回5位数字。 请保留选中的这些5位数字,然后单击 主页 > 数字格式 框> 短日期 将它们转换为日期。 看截图:

现在,您将看到它已批量添加或减去相同的月份数。 看截图:

在Excel中添加或减去迄今为止的年份

例如,您将在Excel中的一批日期中添加6年份,您可以执行以下操作:

=日期(年(日期)+ 年数,MONTH(日期),天(日期))

1。 选择要放置计算结果的空白单元格,键入公式 = DATE(YEAR(A2)+ 6,MONTH(A2),日(A2)),然后按 输入 键。
注意:要从日期中减去6年,请应用此公式 = DATE(YEAR(A2)-6,MONTH(A2),日(A2)).

2。 如果需要,拖动公式单元格的“自动填充”手柄以根据需要将此公式应用于其他单元格。

在Excel中添加或减去年,月和日的组合

在某些情况下,您可能需要同时添加或减去年,月和日,表示3年增加5个月和15天。 在这种情况下,您可以应用DATE函数来解决问题。

=日期(年(日期)+ 年数,MONTH(日期)+ 几个月, 天(日期)+ 天数)

1。 选择要放置计算结果的空白单元格,键入公式 =DATE(YEAR(A2)+3,MONTH(A2)+5,DAY(A2)+15),然后按 输入 键。 看截图:
注意:要同时减去年,月和日的组合,请使用此公式 =DATE(YEAR(A2)-3,MONTH(A2)-5,DAY(A2)-15).

2。 如果需要,请拖动此公式单元格的自动填充句柄以将此公式应用于其他单元格。

使用Kutools for Excel添加或减去迄今为止的天,周,月或年

如果你安装了Kutools for Excel,你会发现它 配方助手 列出了最常用的公式,以帮助您在Excel中轻松计算,包括添加日期,周,月或年。 请做如下:

Kutools for Excel - 包括多个用于Excel的300便捷工具。 全功能免费试用60天,无需信用卡! 现在加入

1。 选择要计算结果的单元格,然后单击 Kutools > 配方助手 > 添加至今的年份.
小技巧:请从中选择合适的配方 配方助手 根据您的需求下拉列表。 例如,选择 添加数周至今 用于添加/减去指定的星期到一个日期。

2。 在打开的“公式助手”对话框中,指定日期单元格地址 约会时间 框,然后键入要添加的年数 框。
小技巧:在我们的例子中,我们将从日期开始添加5年份,因此我们输入 5 框。 如果您需要减去某些年份,例如3年,请在-3中键入 框。

3。 点击 Ok 按钮来应用公式 添加至今的年份。 如果需要,请拖动“填充处理”将该公式应用到其他范围。

添加/减去日期:

添加/减去至今的星期:

迄今加/减月份:

Kutools为Excel的 配方助手 将保存Excel用户不记住复杂的公式,并列出最常用的公式,以帮助您快速计算和解决Excel中的问题, 添加年/月/周/日期, 添加到目前为止的小时/分钟/秒等等。 免费试用!

演示:在Excel中添加或减去日期,周,月或年


300工具可以帮助您脱颖而出

与Kutools for Excel - 从不担心裁员

Kutools for Excel为300工作场景带来了1500便捷工具,帮助您比同事更快更好地工作,轻松赢得老板的信任。 您将成为裁员名单中的最后一名,并为您的家人轻松维持稳定和美好的生活!

  • 在3分钟成为Excel的掌握者,轻松赢得别人的赞赏。
  • 提高80%的工作效率,解决Excel中的80%问题,而不是加班。
  • 加快工作速度,每天节省2小时数,以改善自己,陪伴家人。
  • 告别复杂的公式和VBA代码,释放你的记忆。
  • 减少数以千计的键盘操作和鼠标点击,远离鼠标手。
  • 花费$ 39.0,价值超过$ 4000.0其他人的培训。
  • 选择110,000 +高效人才和300 +知名公司,在Excel中稳定工作。
  • 全功能免费试用60天,无需信用卡。
阅读更多 ...
现在免费试用
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    shalu · 1 years ago
    i need your help, i want to solve one question please tell me
    one boy is going on leave from 26-06-2018 to 22-07-2018 for 27 days and his total monthly salary is 23968

    if the month has 30 days then his salary will be per day 798.93 & if month has 31 days then 773.16

    he was on leave in july 05 days(30 days in the month) and rest 22 dyas in july(31 days in the month)
    23968/30*5+23968/31*44
    i want then formula for this calculation in one cell please tell me as soon as possible because its urgent for me mail me on my personal mail
  • To post as a guest, your comment is unpublished.
    Jeff · 1 years ago
    I used the DateDif to get the number of years, months, and days. Now I'm trying to total all of those years months and days. How to I do the total of all the columns for years months and days
  • To post as a guest, your comment is unpublished.
    Brad J · 1 years ago
    I am trying to use the add month function, but I want the date to be based on the previous tab, so when I copy a new tab, it automatically updates the date based on adding a month to the previous tab's date. Any suggestions? Thank you - Brad
  • To post as a guest, your comment is unpublished.
    shashikant · 1 years ago
    i have a date but i want to be after 120 days, date ..what i mean to say for example ...date 7.11.2017 + days 120 = ?(here which date) ..which formula will i use ?
    • To post as a guest, your comment is unpublished.
      Tang Kelly · 1 years ago
      Hi,
      If the date is in a certain cell, say A1, you can use the formula =A1+120 directly;
      If the date is not in a cell, you can use this formula =DATE(2017,11,7)+120
  • To post as a guest, your comment is unpublished.
    WILLIM · 2 years ago
    For simple calculations
    1 year = 365.25 days
    1 month = 365.25/12
    ...
    cell C1 =now()
    Cell C4 = date in question
    =IF(C4
  • To post as a guest, your comment is unpublished.
    Sangeetha · 2 years ago
    hi,

    I need help regarding dates
    I don't have kutools in my excel

    I want to add number of years with if condition, say 3 years for particular date mentioned in cell C4 having condition if the date mentioned in C4 is less than or equal to today's date/current date.

    I have tried with adding number of days
    =if(C4
  • To post as a guest, your comment is unpublished.
    Willim · 2 years ago
    Hopefully this assists with add or subtracting days, months or years. The formula in D4 is copied to all the other rows and the results are as shown. As can be seen day,months, years or combinations can be added or subtracted

    I am using Open Office but I expect it to work in Excel although perhaps not for before 1900?

    A B C D
    1 dd mm yyyy date
    2 21 4 1853 21-Apr-1853 =DATE(C2;B2;A2) "fixed date"
    3
    4 -15 -8 -63 06-Aug-1789 =DATE(C$2+C4;B$2+B4;A$2+A4) "new variable date"

    -15 0 0 06-Apr-1853 days
    0 -8 0 21-Aug-1852 months
    0 0 -63 21-Apr-1790 years

    15 8 63 05-Jan-1917

    15 0 0 06-May-1853 days
    0 8 0 21-Dec-1853 months
    0 0 63 21-Apr-1916 years
  • To post as a guest, your comment is unpublished.
    Ritika · 3 years ago
    What if we need to subtract months from year

    Say,
    If we have start date 2016
    and the resutant cell we need is Sept 2015.
  • To post as a guest, your comment is unpublished.
    Ritika · 3 years ago
    What if we need to subtract specified number of months from particular Date?
    Say, 2016 we want to fix a formula to get Sept 2015
  • To post as a guest, your comment is unpublished.
    Megha · 3 years ago
    Thank you so much for such a useful content.
    I am facing a difficulty, If I am applying formula "=MONTH(A4-A3)" with A4 as 01/10/2016 & A3 as 01/07/2016, it is returning value 4 instead of 3. Why? kindly explain & how to deal with it.
  • To post as a guest, your comment is unpublished.
    Matt · 3 years ago
    This was exactly what I was looking for - thank you so much!

    One request for help - In the US, when someone dies, the fiduciary tax return is due the following year on the last day of the month prior to the date of the person's death. So if the person dies March 15, 2015, then the tax return will be due on February 29, 2016. Is there a way to calculate that and display it in a cell?

    Thanks,

    Matt
    • To post as a guest, your comment is unpublished.
      · 2 years ago
      =EOMONTH("2015/3/15",11)
    • To post as a guest, your comment is unpublished.
      star · 3 years ago
      Formula is EOmonth(3/15/2015,11)
  • To post as a guest, your comment is unpublished.
    soso · 3 years ago
    very very interesting
  • To post as a guest, your comment is unpublished.
    Graeme · 3 years ago
    Hi Guys

    Can anyone help me continue the days of the week from a starting point that will auto calculate to other tabs in excel please?
  • To post as a guest, your comment is unpublished.
    Champika · 3 years ago
    This is great. Thank you for explaining it in an easy to understand steps.
  • To post as a guest, your comment is unpublished.
    ibasheer · 3 years ago
    Fantastic.....u made my job easy now..
    Thanks a lot
  • To post as a guest, your comment is unpublished.
    Sam · 3 years ago
    How do I write a formula from numbers of days an apartment is vacant to find out what was the original date that it was vancated?
  • To post as a guest, your comment is unpublished.
    pallavi · 3 years ago
    How to add two time periods,
    means, i have 2 years 5 months experience in past, present i have 1 year 2 months experience,
    How to add those two periods in excel.
  • To post as a guest, your comment is unpublished.
    Kim · 3 years ago
    That did not work at all
  • To post as a guest, your comment is unpublished.
    Mounika · 3 years ago
    Really Thanks a lot...Helped this Tip in awesome way.
  • To post as a guest, your comment is unpublished.
    vaishnavi · 3 years ago
    it was very helpful for me
  • To post as a guest, your comment is unpublished.
    suhier · 3 years ago
    A lot of thanks for your great effort :) but I want your kind help to explain the following ..
    What is the formula I have to use in order to automatically calculate a certain credit in a given range ( date from- to) ??
    For example I need to create a vacation system ; that gives an employee a certain credit of 28 days to be taken in 2 years ONLY, if he exceeds 2 years he will not be entitled to ask for vacation but he can use the new credit of the next 2 years & etc.. .

    Thank you for your time & consideration .
  • To post as a guest, your comment is unpublished.
    AMitesh · 3 years ago
    Hi, if i have a date in one cell (E.g 20-11-2015) and i want to add 7 days to that date as a reminder in another cell (i.e 20-11-2015+ 7 days= 27-11-2015). What is the formula i have to use to get result as 27-11-2015. Please assist.
  • To post as a guest, your comment is unpublished.
    RAJ · 4 years ago
    The formula you give works with recent dates, but will not calculate with older dates. For example, If I have a date of Jan 9, 1896 and want to subtract 60 years 9 months 30 days it does not work. It will say #VALUE!. If you change the year to 1996 it works fine. Can this be fixed?
  • To post as a guest, your comment is unpublished.
    Senthilvel · 4 years ago
    Thank u so much!! worked well.
  • To post as a guest, your comment is unpublished.
    Jaypee · 4 years ago
    Thank you so much! really nice!
  • To post as a guest, your comment is unpublished.
    CeRo · 4 years ago
    was great, very helpful to me!!!
  • To post as a guest, your comment is unpublished.
    Naseeb · 4 years ago
    excellent explanation
    But i need to know on below in case i need result date to be end of every month ..What formula to use
    Examble 1-May-2015 to be converted to 30 Apr 2016

    Also 1 Jun 2015 to be converted to 31 May 2016
    PLease help
  • To post as a guest, your comment is unpublished.
    Nilesh · 4 years ago
    Excellent and good Explanation in details..nice thank you so much
  • To post as a guest, your comment is unpublished.
    Tapas Behera · 4 years ago
    All these are good except for one - add years to date. I am not saying your solution is wrong, but there can be another solution based on the need. In case you are adding 1 year to 29th Feb 2016. Your formula will give the date 1st Mar 2017, whereas someone may look for the the end date of Feb itself. For them the below formula would work better.

    =EDATE(A2,12)
  • To post as a guest, your comment is unpublished.
    Madhu Nair · 4 years ago
    how to put date of 2015 in excel
  • To post as a guest, your comment is unpublished.
    dianne · 4 years ago
    I have dates going across a sheet; each column will eventually have a date put in it. What I want to do is have the last column-which is labeled 'Next Due Date' automatically add 90 days to the newest date entered.
    Columns are labeled 'Date Completed'
    There will be a list of names in the rows and as their Home Visits are completed and the date is entered, we want the last column to 'refresh' & tell us the next due date (90 days)
    All I can find is: example =D2+90 but then we have to change the Letter each time & we want to protect that column so nobody can mess with the formula.
  • To post as a guest, your comment is unpublished.
    AnneK · 4 years ago
    Avinash, if you know for sure that it will be the first of the month each time:
    =ROUND((L5-K5)/30,0)-1
    Otherwise, you can try this: =CONVERT(L5-K5,"day","yr")*12 but then you still need to round it accordingly. I admit that neither of the solutions is really pretty.
  • To post as a guest, your comment is unpublished.
    VENKAT · 4 years ago
    03.02.2014 how convert 03/02/2014
  • To post as a guest, your comment is unpublished.
    VENKAT · 4 years ago
    03.02.2014 how to convert 03/02/2014
  • To post as a guest, your comment is unpublished.
    Avinash Singh · 4 years ago
    Hello..

    I want to less year to year get months in excel,Exp:-01-01-2000 - 01-08-2000 = 8 months
    by which formula i do that>>>.
  • To post as a guest, your comment is unpublished.
    Sam Trieu · 4 years ago
    This is why I like Excel. Thank You for sharing. Save me a lot of time when I compute depreciation expenses for my company
  • To post as a guest, your comment is unpublished.
    Prashant Chitmulwar · 4 years ago
    Sir,
    Very informative and helping a lot.
    Thank You.
  • To post as a guest, your comment is unpublished.
    Azeem Qureshi · 4 years ago
    20/08/2014 - 10/10/2016=?

    =DATEDIF(A1,B1,"Y")&"Years,"&DATEDIF(A1,B1,"YM")&"Months"&DATEDIF(A1,B1,"MD")&"Days"
    If it not working than go to control panel then go to clock, language, Region and set your date format like 1st day, Month, Year and ok.
    It was the solution Ravi Kumar of your question.
    Reagrad
    Azeem Qureshi
  • To post as a guest, your comment is unpublished.
    Anita · 4 years ago
    thank you!!!!! you saved me!
  • To post as a guest, your comment is unpublished.
    Anil Pawaskar · 5 years ago
    Very Nice
    I am thankful
  • To post as a guest, your comment is unpublished.
    Ravi Kumar · 5 years ago
    Dear Sir/Madam,

    20/08/2014 - 10/10/2016 = days ...?

    In excel how to calculate the no of days help me in this regard.

    Regards
    Cherry,
    • To post as a guest, your comment is unpublished.
      star · 3 years ago
      In order to cal days try this formula Datedif(10/10/2016-20/8/2014,"d")+1
  • To post as a guest, your comment is unpublished.
    Lei G · 5 years ago
    THIS SIN'T WHAT I WAS LOOKING FOR, IT WASN'T USEFUL AT ALL. I WAS TRYING TO CALCULATE A TIME PERIOD . NOT SUBTRACT & ADD DATES ! THANKS BUT NO THANKS :-x
  • To post as a guest, your comment is unpublished.
    Pankaj Singh · 5 years ago
    Extremely Good..... Looking for these kind of more examples
  • To post as a guest, your comment is unpublished.
    Pankaj Singh · 5 years ago
    Really Cool yaar...... Extremely Good
  • To post as a guest, your comment is unpublished.
    kunnumpurathu · 5 years ago
    Simple but effectve explanation; thanks
  • To post as a guest, your comment is unpublished.
    Ajeet · 5 years ago
    Really so nice post... helped a lot
  • To post as a guest, your comment is unpublished.
    Rama · 5 years ago
    Thank you so much for your explaining with screen shots.
    Really so much helped this tip to me..
  • To post as a guest, your comment is unpublished.
    NIK · 5 years ago
    thanks EDATE function solved my problem

    thanks a lot.
  • To post as a guest, your comment is unpublished.
    Sneet · 5 years ago
    This is what I was looking for. Thanks, it is really useful. :-)
  • To post as a guest, your comment is unpublished.
    Lizzy · 5 years ago
    Thank you, thank you, thank you. This is exactly what I needed and explained in such a clear manner with explanations and examples. Has made life so much easier without my brain imploding :lol: