Subtracting a given amount of days from a date.

**WTB** · 03-29-2010, 08:05 AM

Hi All,

I've got a spreadsheet with various dates on it and i want to include a column which will remind me of the renewal date. So for an example if i had an expiry date of 1/5/10 in the reminder column i would like it to show that date minus a given amount of days for example 30 days so 1/4/10? What formula would i use in the reminder column to achieve this?

Actually thinking about it a bit more, it would probably be better if i had a column with the days left on the contract, for example 365, which would reference the current date (using TODAY) and then i could apply conditional formatting to the cell which would in turn highlight the cell red when less than 30 days until renewal. Any ideas on this?

Preferably the later would be best, but i'd be interested top know how to minus a given amount of days from a date for future reference.

Thanks in advance and i hope everyone is looking forward to back to back 4 day weeks as much as i am!

WTB

**nchavan** · 03-29-2010, 08:11 AM

Hi WTB,

To perform this task, as per your explanation

If cell A1 is the date like 1/5/2010 and you wish to subtract 30 days from the date then in the reminders column write this simple function:

=A1-30 this will give you the result as 1/4/2010.

Hope this has clarified your doubt.

**WTB** · 03-29-2010, 08:14 AM

Thanks Neal, that makes me feel really stupid! Obviously i was trying to overcomplicate it! Any ideas on a formula to show the days left in a contract?

WTB

**nchavan** · 03-29-2010, 08:35 AM

That you can show by renewal date-today's date.

For example: if the renewal date is 1/4/2010 and today's date is 29/3/2010 then subtract today's date from renewal date i.e. 1/4/2010 minus 29/3/2010 the result should be 3. And also set the result cell format to number.

This would give you your desired result.

**Moondog** · 03-29-2010, 09:55 AM

If instead of manually entering today's date in a cell you use =TODAY(), the date will automatically be updated each time you open the spreadsheet and the calculations of days remaing will also similarly update. But don't forget to format the cell to 'Date'.