Hello
I have created a spreadsheet using Excel 2010 to show when tools have been PAT tested and calibrated, also I have put in dates to show when they are due for renewal. Please can someone advise any formulas I could try to flag up when the renewals are due a month before the renewal date?
Thank you.
One way, for a date in cell A1:
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thanks for your reply, unfortunately this is not quite what I required. Sorry if I didn't explain it clearly.
This is the formula I have at the moment.
=IF(ISBLANK(D3),"-",IF((D3="complete"),"complete",IF((D3>TODAY()),"on track",IF((D3=TODAY()),"due",IF((D3<TODAY()),"overdue")))))
I want it to flag up that it is 'due' 1 month before the date that is entered in cell D3. So for example if something has an expiry date of 25-Dec-2013, when it gets to the date 25-Nov-2013 and after I want it to be displayed as due? Hope this is clearer and someone can help.
Thank you.
Have a look at the example workbook attached.
It uses the formula:
E3:Formula:Please Login or Register to view this content.
Cell E1 containsFormula:Please Login or Register to view this content.
Last edited by TMS; 11-21-2013 at 09:07 AM. Reason: Amend cell reference
@Charlene C:
I notice you have been on the board since I offered this solution. As I spent some time putting it together for you, I would be very interested to know if it worked and did what you hoped for.
Regards, TMS
Hello
I have only been on here to view your comments. I have tried this formula earlier and it came up with the below error and I didn't understand what needed to change unfortunately? If you could explain that would be much appreciated.
Thank you.
print screen excel warning.docx
Probably my fault. Initially I had said put the formula in cell D3 ... but then realised my mistake and changed it to E3.
So, putting a formula that refers to cell D3 in cell D3 will get you the circular reference warning.
Have a look at the example ... you'll see the formula is actually in cell E3.
Dohhh ... sorry.
Regards, TMS
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks