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

or

如何计算/计算Excel中两个日期之间的周/月/年?

你有没有考虑过计算Excel中两个给定日期之间的几周,几个月或几年? 本教程可以帮助您尽快完成以下操作。

计算/计算两个日期之间的星期

计算/计算两个日期之间的月份

计算/计算两个日期之间的年份

计算/计算两个日期之间的月份和日期


添加一些年/月/日/周至今:

Kutools for Excel公式 实用程序包含一些常用公式,例如添加日期,基于生日计算年龄等。 用这些通常的公式,你可以很快得到你想要的结果,而不用记住任何公式。

DOC-数周,二,dates3

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


箭头蓝色右泡 计算/计算两个日期之间的星期


要计算两个日期之间有多少星期,您可以使用下面的方便公式,只需从结束日期中减去开始日期并除以7即可:

1。 输入这个公式 =(B2-B1)/ 7 进入空白单元格,(B2 表示结束日期和 B1 是开始日期),然后按 输入 键,您将得到在两个日期之间有18星期和更多4天的特定结果,请参阅截图:

doc计算周数两个日期1

注意:如果你只想整整一周,请申请 INT 功能: = INT((B2-B1)/ 7).

doc计算周数两个日期2


箭头蓝色右泡 计算/计算两个日期之间的月份

如果你想确定两个日期之间的月数,你可以使用这个 DATEDIF 功能。

1。 请输入此公式 = DATEDIF(B1,B2, “M”) 进入空白单元格(B1 代表开始日期和 B2 指示结束日期),然后按 输入 键,您将获得两个给定日期之间的月数,请参阅截图:

doc计算周数两个日期3


箭头蓝色右泡 计算/计算两个日期之间的年份

有了以上 DATEDIF 函数,还可以计算两个日期之间的年数。

1。 应用这个公式 = DATEDIF(B1,B2, “y”)的 (B1 代表开始日期和 B2 指示结束日期),然后按 输入 键,您将获得两个日期之间的总年数。 看截图:

doc计算周数两个日期4


箭头蓝色右泡 计算/计算两个日期之间的月份和日期

有时,您需要确切知道两个日期之间有多少年,几个月和几天,在这种情况下,以下长公式可能会对您有所帮助。

1。 在空白单元格中输入这个长公式 = DATEDIF(B1,B2,“Y”)和“年”,以及DATEDIF(B1,B2,“YM”)和“Months”和DATEDIF(B1,B2,“MD”.然后点击 输入 键,可以立即计算日期之间的具体年数,月份和日期,请参见截图:

doc计算周数两个日期5

注意:在上面的公式中, B1 表示开始日期和 B2 代表结束日期,您可以根据需要修改它们。


转换时间为小时/分钟/秒:

如果您需要将时间转换为工作表中的小时,分​​钟或秒钟,那么 Kutools for Excel转换时间 功能可能会帮助您快速处理此任务。

doc转换时间1 1

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 下载并免费试用现在!


相关文章:

如何在Excel中添加/减去日期/月/年?

如何统计Excel中两个日期之间的特定工作日数量?

如何计算/计算Excel中两个日期之间的天数?


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
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.
    338s · 1 years ago
    Avenger vs Marvell
    perhitungan permainan roulette
    perhitungan permainan sicbo
  • To post as a guest, your comment is unpublished.
    corn · 1 years ago
    How can obtain age from two DOB. Eg- i need to calculate the age from DATE of Birth till the Date of Treatment was started
  • To post as a guest, your comment is unpublished.
    vishnu · 1 years ago
    how to obtain matching no of months between two sets of dates ?

  • To post as a guest, your comment is unpublished.
    Rhea Mae Escobal · 2 years ago
    What is the formula for 01/05/2017 13:01:20 minus 01/05/2017 08:59:27 to get the total hours.
  • To post as a guest, your comment is unpublished.
    jo martino · 2 years ago
    I need the following and cannot figure out the formula
    I have been given the date of birth, date of exam. I need a third date, (record purge date) that
    has the following rules;

    IF they are 18 or over by exam date, the purge date will be 7 years from exam date
    IF they are under 18 by exam date, the purge date would be 7 years from when they turn 18
  • To post as a guest, your comment is unpublished.
    ananan · 2 years ago
    Thank You so much for your way.

    2017 calendar printable
  • To post as a guest, your comment is unpublished.
    Manohar Meena · 2 years ago
    Thank You so much for your way of displaying the information pictorially. It was so helpful to me for counting YYMMDD in a single cell.
  • To post as a guest, your comment is unpublished.
    Derek · 2 years ago
    The formula for the number of weeks between two dates is not correct. It just tells us how many seven day periods there were. Weeks start on Sunday and end on Saturday.

    So a ten day period could be 2 or 3 weeks/parts of weeks.

    [u]Example[/u]:
    Start on Saturday. Saturday is last day of week one. Then the next 7 days are week two. Then there are two days left in week three, ending on Monday.

    [u]Question:[/u]
    Is there a formula to calculate how many weeks or parts of weeks there are between two dates?
  • To post as a guest, your comment is unpublished.
    Pravin · 2 years ago
    required to calculate in datedif function i.e 12.05.84 to 15.05.85 + 01.09.01 to 28.08.03 = result. is it possible?
  • To post as a guest, your comment is unpublished.
    Mannu · 2 years ago
    I want to know that how to count week number when start a month like ([u]1-Nov 2016 than week no - 1 or 29-Nov 2016 than week no - 5[/u])

    Thanks
    Mannu
  • To post as a guest, your comment is unpublished.
    Mike · 3 years ago
    Hi there, anyone can show me how to count number of fortnights between two dates?

    And what if the fortnight was not fully divided between the two dates? Will it show as days remaining?

    Thanks.

    Mike
  • To post as a guest, your comment is unpublished.
    alan millar · 3 years ago
    No mention is made of how the cells should be formatted.

    Also this formula calculates 9th May 2016 minus 23rd June 2015 as 36,473 weeks ??
  • To post as a guest, your comment is unpublished.
    NIMFA SALDUA · 3 years ago
    Hello, pls. help me. I have to know the formula for start date and ending date with a revision of the ending date. Example:

    Spreadsheet:

    A(start date) B (end date) C (end date-revision)
    February 1, 2016 February 26, 2016 February 19, 2016

    Pls. help formulate the formula for these 3 columns in the spreadsheet.

    Thank you.

    nimfa
  • To post as a guest, your comment is unpublished.
    Dave · 3 years ago
    Copied formula, used 6/20/1949 and 1/4/2016, it gave me 66 yrs 6 months 128 days. if I use a start day date higher than the end day date I get triple digits, such as 6/5 to 6/30. Is it a formatting issue?
  • To post as a guest, your comment is unpublished.
    Pavan Kumar · 4 years ago
    Datedif is very useful for Years,Months and days.

    Thank you very munch
  • To post as a guest, your comment is unpublished.
    jagdish · 4 years ago
    15/04/1996 and 01/01/2016 how to calculate year by function =datedif
  • To post as a guest, your comment is unpublished.
    yda · 4 years ago
    thanks a lot very usful information
  • To post as a guest, your comment is unpublished.
    James Kong · 4 years ago
    Datedif was very useful for Years,Months and the days.
  • To post as a guest, your comment is unpublished.
    Sopheak · 4 years ago
    I used the formula "Datedif" both for Month and year, it's missed one Month or one Year. Eg. 01/Jan/2014 and 31/Dec/2014 the logical is 12 Months but the answer of the formula is 11 Months (Wrong).
    Please help to check and explain,

    Sopheak
    • To post as a guest, your comment is unpublished.
      Gary · 2 years ago
      Start Date: 1-Jan-2014
      End Date: 31-Dec-2014
      Days: 364.00 or 0 Years, 11 Months, 30 Days
      • To post as a guest, your comment is unpublished.
        SELVAM · 2 years ago
        had a similar problem and I tried this and seems to work.

        =DATEDIF(D10,E10,"y")&" Yr " &DATEDIF(D10,E10,"ym") & " Mth " &DATEDIF(D10,E10, "md")+1 & " Day"
      • To post as a guest, your comment is unpublished.
        Gary · 2 years ago
        Start Date 1/1/2014
        End Date 1/1/2015
        Days 365 Days
        1 Years, 0 Months, 0 Days =DATEDIF(C25,C26,"M") & " Months, " & DATEDIF(C25,C26,"MD") & " Days"
        12 Months, 0 Days =DATEDIF(C25,C26,"M") & " Months, " & DATEDIF(C25,C26,"MD") & " Days"
        • To post as a guest, your comment is unpublished.
          Syed · 2 years ago
          As ur way to we get ans this:
          1 Years, 0 Months, 0 Days
          12 Months, 0 Days

          But
          how can we add those two thing in another column.
          can u explain and share tht formula

          For Example:
          A1:1 Years, 0 Months, 0 Days
          A2:12 Months, 0 Days
          A3:????????????????
          how we get the total?
    • To post as a guest, your comment is unpublished.
      Gary · 2 years ago
      =DATEDIF(C25,C26,"Y") & " Years, " & DATEDIF(C25,C26,"YM") & " Months, " & DATEDIF(C25,C26,"MD") & " Days"

      Start Date: 1/1/2014
      End Date: 31/12/2014
      Days 364.00 or 0 Years, 11 Months, 30 Days
    • To post as a guest, your comment is unpublished.
      Abby · 4 years ago
      I have the same problem also.
  • To post as a guest, your comment is unpublished.
    amit · 4 years ago
    I want to know what will be the year form today after 15 years
  • To post as a guest, your comment is unpublished.
    Mushi Gao · 4 years ago
    I need to write about which less (date).
  • To post as a guest, your comment is unpublished.
    SUDHAKAR · 4 years ago
    is it work in any date format? Ex. dd/mm/yyyy, mm/dd/yyyy, yyyy/mm/dd
    Please reply
    • To post as a guest, your comment is unpublished.
      Kisim · 4 years ago
      [quote name="SUDHAKAR"]is it work in any date format? Ex. dd/mm/yyyy, mm/dd/yyyy, yyyy/mm/dd
      Please reply[/quote]
      [quote name="SUDHAKAR"]is it work in any date format? Ex. dd/mm/yyyy, mm/dd/yyyy, yyyy/mm/dd
      Please reply[/quote]
      Work format is dd/mm/yyy
  • To post as a guest, your comment is unpublished.
    kavi · 4 years ago
    want to calculate months between two dates
  • To post as a guest, your comment is unpublished.
    vipul · 4 years ago
    194 days to be converted in month (how many months)

    please suggest formula for same in excel
  • To post as a guest, your comment is unpublished.
    Kisim · 4 years ago
    Need a formula to return week pacific month such as bellow
    1st week
    2nd week
    3rd week
    4th week
    Then go to next moth automatically changed return same result
    • To post as a guest, your comment is unpublished.
      Prabu · 4 years ago
      =INT((A2-(DATE(YEAR(A2),MONTH(A2)-MOD(MONTH(A2),1),1)-WEEKDAY(DATE(YEAR(A2),MONTH(A2)-MOD(MONTH(A2),1),1),2)+1)+7)/7)
  • To post as a guest, your comment is unpublished.
    Vijay Hudda · 4 years ago
    Formula can't working in Excel sheet
  • To post as a guest, your comment is unpublished.
    GT · 4 years ago
    Further to your =DATEDIF(B1,B2,"Y") & " Years, " & DATEDIF(B1,B2,"YM") & " Months, " & DATEDIF(B1,B2,"MD") & " Days" formula, I have a requirement for Months, Weeks and Days. Is this possible ?
  • To post as a guest, your comment is unpublished.
    Ramchandra · 5 years ago
    A very very thanks to Extend office to help me out of this issue
  • To post as a guest, your comment is unpublished.
    Ayyanar · 5 years ago
    Thank you extend office
    This is very very usefull function
  • To post as a guest, your comment is unpublished.
    Deen · 5 years ago
    That was very easy and helpful. I have a question however. What if in your above example B2 is a constant. Therefore, what if I am trying to find the number of year/months/days between start date and TODAY or a fixed date. Can I add that date in the formulae rather than making a new cell with the date and having the formula read from that?