Microsoft Office Forums A Formula Request
 Register FAQ Search Today's Posts Mark Forums Read

#1
10-07-2015, 08:22 AM
 Planner18 Windows 8 Office 2010 32bit Novice Join Date: Oct 2014 Posts: 13
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.
#2
10-08-2015, 03:39 PM
 JulieS Windows 7 64bit Office 2013 Expert Join Date: Dec 2011 Location: New England Posts: 1,696

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")
#3
10-12-2015, 06:32 AM
 Planner18 Windows 8 Office 2010 32bit Novice Join Date: Oct 2014 Posts: 13

Thank you for your response. It is much appreciated
#4
01-03-2017, 05:55 AM
 Planner18 Windows 8 Office 2010 32bit Novice Join Date: Oct 2014 Posts: 13

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
#5
01-03-2017, 02:09 PM
 Guloluseus Windows 7 64bit Office 2010 32bit Competent Performer Join Date: Mar 2013 Posts: 154

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.
#6
01-04-2017, 01:42 AM
 Planner18 Windows 8 Office 2010 32bit Novice Join Date: Oct 2014 Posts: 13

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.
#7
01-08-2017, 12:41 PM
 Guloluseus Windows 7 64bit Office 2010 32bit Competent Performer Join Date: Mar 2013 Posts: 154

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!
#8
01-09-2017, 02:18 PM
 Planner18 Windows 8 Office 2010 32bit Novice Join Date: Oct 2014 Posts: 13

Hi Guloluseus,

I have copied and pasted and the formula works fine.

Thank you very much for all your help.

Regards

Planner18
#9
01-10-2017, 01:16 PM
 Guloluseus Windows 7 64bit Office 2010 32bit Competent Performer Join Date: Mar 2013 Posts: 154

Itsvery easy to mistype, especially with commas and brackets everywhere!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Raza Word VBA 1 01-09-2015 04:10 AM OTPM Excel 16 12-16-2013 01:57 PM JAMS Excel 2 04-06-2012 10:14 PM steve42399 Outlook 2 04-12-2011 09:35 AM coolpeter86 Outlook 0 12-12-2010 11:58 PM

All times are GMT -7. The time now is 01:38 PM.

 -- Default Style -- Mobile Style Contact Us - Privacy Statement - Top