is there a way in Excel to have a formula that does something like this:
Excel中有没有这样的公式:
= 12/20/2010 - 180
= 12/20/2010 - 180
which would take a certain date (12/20/2010 in this case) and subtract 180 days . .
这需要一个特定的日期(在本例中是12/20/2010)，减去180天。
5 个解决方案
#1
11
Assuming the original date is in cell A1:
假设原始日期在单元格A1中:
=DATE(YEAR(A1), MONTH(A1), DAY(A1)-180)
#2
5
Assuming the original date is in cell A1:
假设原始日期在单元格A1中:
=A1-180
Works in at least Excel 2003 and 2010.
至少在2003年和2010年工作。
#3
4
You can paste it like this:
你可以这样粘贴:
= "2010-12-20" - 180
And don't forget to format the cell as a Date [CTRL]+[F1] / Number Tab
不要忘记将单元格格式化为日期[CTRL]+[F1] / Number选项卡
#4
2
Here is what worked for me (Excel 14.0 - aka MS Office Pro Plus 2010):
以下是对我有用的东西(Excel 14.0 -即MS Office Pro Plus 2010):
=DATE(YEAR(A1), MONTH(A1), DAY(A1) - 16)
This takes the date (format mm/dd/yyyy) in cell A1 and subtracts 16 days with output in format of mm/dd/yyyy.
这使用单元格A1中的日期(格式为mm/dd/yyyy)，减去16天，输出格式为mm/dd/yyyy。
#5
0
Say the 1st date is in A1 cell & the 2nd date is in B1 cell
第一次约会是在A1单元，第二次约会是在B1单元。
Make sure that the cell type of both A1 & B1 is DATE.
Then simply put the following formula in C1:
确保A1和B1的细胞类型都是日期。然后在C1中输入如下公式:
=A1-B1
The result of this formula may look funny to you. Then Change the Cell type of C1 to GENERAL.
你可能觉得这个公式的结果很有趣。然后将C1的单元格类型更改为GENERAL。
It will give you the difference in Days.
它会给你几天的不同。
You can also use this formula to get the remaining days of year or change the formula as you need:
你也可以用这个公式得到一年剩余的天数或者根据你的需要改变公式:
=365-(A1-B1)
#1
11
Assuming the original date is in cell A1:
假设原始日期在单元格A1中:
=DATE(YEAR(A1), MONTH(A1), DAY(A1)-180)
#2
5
Assuming the original date is in cell A1:
假设原始日期在单元格A1中:
=A1-180
Works in at least Excel 2003 and 2010.
至少在2003年和2010年工作。
#3
4
You can paste it like this:
你可以这样粘贴:
= "2010-12-20" - 180
And don't forget to format the cell as a Date [CTRL]+[F1] / Number Tab
不要忘记将单元格格式化为日期[CTRL]+[F1] / Number选项卡
#4
2
Here is what worked for me (Excel 14.0 - aka MS Office Pro Plus 2010):
以下是对我有用的东西(Excel 14.0 -即MS Office Pro Plus 2010):
=DATE(YEAR(A1), MONTH(A1), DAY(A1) - 16)
This takes the date (format mm/dd/yyyy) in cell A1 and subtracts 16 days with output in format of mm/dd/yyyy.
这使用单元格A1中的日期(格式为mm/dd/yyyy)，减去16天，输出格式为mm/dd/yyyy。
#5
0
Say the 1st date is in A1 cell & the 2nd date is in B1 cell
第一次约会是在A1单元，第二次约会是在B1单元。
Make sure that the cell type of both A1 & B1 is DATE.
Then simply put the following formula in C1:
确保A1和B1的细胞类型都是日期。然后在C1中输入如下公式:
=A1-B1
The result of this formula may look funny to you. Then Change the Cell type of C1 to GENERAL.
你可能觉得这个公式的结果很有趣。然后将C1的单元格类型更改为GENERAL。
It will give you the difference in Days.
它会给你几天的不同。
You can also use this formula to get the remaining days of year or change the formula as you need:
你也可以用这个公式得到一年剩余的天数或者根据你的需要改变公式:
=365-(A1-B1)