Excel 2007: How Do I add a 'days since' formula?

BenHoughton

New Member
Joined
Mar 26, 2013
Messages
5
Hi all,

First post, please be gentle :)

I've created the structure of an excel spreadsheet I wish to use but I'm stuck now with formulas as I've never really used them before. Certainly I've never actually created one! How can I add a formula to a cell to highlight if the date in the cell above is over 60 and 90 days?

I've seen the flag icons in the 'conditional formatting' and ideally I'd be able to set:

Green flag for anything under 60 days
Amber flag for anything over 60 days
Red flag for anything over 90 days

Any help would be greatly appreciated?

On a similar vein, is it possible to 'lock' editing of the formulas as the spreadsheet will be sent around work and to be honest I'd rather not end up having to fix the broken formulas further down the line :p

Thank you in advance :)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
blimey, thanks for the quick response!

I meant if I wanted to add a flag to say that it has been 60 days since 26/03/2013 which formula would I use?

, do I just add this into the cell or do I need to add it to the formatting area of excel?

Thanks in advance again :)
 
Upvote 0
Select the cell with your date, go to Format, Conditional formatting ...

Click the + button to add a new conditional format rule for the current selection

You should find an option to enter a formula to determine the condition, where that selection is depends on your version of Excel. It is under the "classic" style option on more recent versions.

If your date cell is A1 add the following three conditional format rules:

=(TODAY()-A1)<60 and choose a green format
=AND((TODAY()-A1)>=60,(TODAY()-A1)<=90) and choose an amber / yellow format

=(TODAY()-A1)>90 and choose a red format

You can copy and paste this formatting to any other cell you want to behave in a similar way.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,207,259
Messages
6,077,347
Members
446,279
Latest member
hoangquan2310

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top