excel formula to subtract number of days from a da

Question

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 . .

Answer 1

Assuming the original date is in cell A1:

=A1-180

Works in at least Excel 2003 and 2010.

Answer 2

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.

Answer 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

Answer 4

Assuming the original date is in cell A1:

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

Answer 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)