Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Office > Project

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-07-2015, 08:22 AM
Planner18 Planner18 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Oct 2014
Posts: 12
Planner18 is on a distinguished road
Default A Formula Request

Hi All,

I'm looking for a formula that will change graphical indicators before 'todays date' with the following outcome.

10 days or more before todays date = green
10 to 6 days before todays date = amber
5 days or less before todays date = red


100% cmpl = blue.

I hope I have explained this correctly and it is not unusual request as this formula may have been provided previously.

Any help will be gratefully received.

Thanks

Last edited by Planner18; 10-08-2015 at 12:19 AM.
Reply With Quote
  #2  
Old 10-08-2015, 03:39 PM
JulieS JulieS is offline Windows 7 64bit Office 2013
Moderator
 
Join Date: Dec 2011
Location: New England
Posts: 1,696
JulieS will become famous soon enough
Default

In a text field:

switch([% Complete]=100,"blue",ProjDateDiff([Current Date],[Finish])/[Minutes Per Day]<=5,"red",ProjDateDiff([Current Date],[Finish])/[Minutes Per Day]<=10,"amber",ProjDateDiff([Current Date],[Finish])/[Minutes Per Day]>10,"green")
Reply With Quote
  #3  
Old 10-12-2015, 06:32 AM
Planner18 Planner18 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Oct 2014
Posts: 12
Planner18 is on a distinguished road
Default

Thank you for your response. It is much appreciated
Reply With Quote
  #4  
Old 01-03-2017, 05:55 AM
Planner18 Planner18 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Oct 2014
Posts: 12
Planner18 is on a distinguished road
Default

Hi,

This formula works great but I need a slight change.

It is possible for the formula to be amended so that if the task name equals "xxx" it will leave the output blank (i.e. not red, amber. etc.)

Any help would be appreciated.

Regards

Planner18
Reply With Quote
  #5  
Old 01-03-2017, 02:09 PM
Guloluseus Guloluseus is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Mar 2013
Posts: 137
Guloluseus is on a distinguished road
Default

Apologies to Julie for taking this one on <g>... To keep it simple, try tis one.

IIf([Name]<>"xxx",switch([% Complete]=100,"blue",ProjDateDiff([Current Date],[Finish])/[Minutes Per Day]<=5,"red",ProjDateDiff([Current Date],[Finish])/[Minutes Per Day]<=10,"amber",ProjDateDiff([Current Date],[Finish])/[Minutes Per Day]>10,"green"),"")

It is the same as the original with a small section before AND after the previous formula, which should be easy to follow.
Reply With Quote
  #6  
Old 01-04-2017, 01:42 AM
Planner18 Planner18 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Oct 2014
Posts: 12
Planner18 is on a distinguished road
Default

Hi Guloluseus,

Sorry to say it didn't work

It turned all the indicators to red but it did remove the indicator with the text name 'xxx'.

Regards

Planner18

Last edited by Planner18; 01-04-2017 at 03:07 PM.
Reply With Quote
  #7  
Old 01-08-2017, 12:41 PM
Guloluseus Guloluseus is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Mar 2013
Posts: 137
Guloluseus is on a distinguished road
Default

I tried the formula on my copy of MSP 10, and it worked fine, then copied and pasted into the post, so it SHOULD work. Did you copy and paste it into the equation box in MSP, or type it manually? If it was typed, try pasting it straight in, that my solve it.

I will try on another computer and see what happens, have known these to work fine on one and not another!
Reply With Quote
  #8  
Old 01-09-2017, 02:18 PM
Planner18 Planner18 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Oct 2014
Posts: 12
Planner18 is on a distinguished road
Default

Hi Guloluseus,

I have copied and pasted and the formula works fine.

Thank you very much for all your help.

Regards

Planner18
Reply With Quote
  #9  
Old 01-10-2017, 01:16 PM
Guloluseus Guloluseus is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Mar 2013
Posts: 137
Guloluseus is on a distinguished road
Default

Glad it worked

Itsvery easy to mistype, especially with commas and brackets everywhere!
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Request for word VBA books Raza Word VBA 1 01-09-2015 04:10 AM
Formula Help Request OTPM Excel 16 12-16-2013 01:57 PM
Formula help request JAMS Excel 2 04-06-2012 10:14 PM
Outlook Calendar request steve42399 Outlook 2 04-12-2011 09:35 AM
Meeting Request - Another Organizer coolpeter86 Outlook 0 12-12-2010 11:58 PM


All times are GMT -7. The time now is 07:29 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft