excel formula to subtract number of days from a da

问题:

is there a way in Excel to have a formula that does something like this:

= 12/20/2010 - 180

which would take a certain date (12/20/2010 in this case) and subtract 180 days . .

回答1:

Assuming the original date is in cell A1:

=DATE(YEAR(A1), MONTH(A1), DAY(A1)-180)

回答2:

Assuming the original date is in cell A1:

=A1-180

Works in at least Excel 2003 and 2010.

回答3:

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

回答4:

Here is what worked for me (Excel 14.0 - aka 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.

回答5:

Say the 1st date is in A1 cell & the 2nd date is in B1 cell

Make sure that the cell type of both A1 & B1 is DATE.
Then simply put the following formula in C1:

=A1-B1

The result of this formula may look funny to you. Then Change the Cell type of C1 to 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)