CODEWITHSUNDEEP
excelexcel-formula
leora
leora

Reputation: 196449

excel formula to subtract number of days from a date

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

Upvotes: 12

Views: 111883

Answers (5)

Craig B
Craig B

Reputation: 333

Assuming the original date is in cell A1:

=A1-180

Works in at least Excel 2003 and 2010.

Upvotes: 5

user3052759
user3052759

Reputation: 1

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)

Upvotes: 0

JD Hill
JD Hill

Reputation: 21

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.

Upvotes: 2

Kevin Crowell
Kevin Crowell

Reputation: 10180

Assuming the original date is in cell A1:

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

Upvotes: 13

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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

Upvotes: 4

Related Questions