Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-07-2015, 08:22 AM
Planner18 Planner18 is offline A Formula Request Windows 8 A Formula Request Office 2010 32bit
Novice
A Formula Request
 
Join Date: Oct 2014
Posts: 14
Planner18 will become famous soon enough
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 A Formula Request Windows 7 64bit A Formula Request Office 2013
Expert
 
Join Date: Dec 2011
Location: New England
Posts: 1,693
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 A Formula Request Windows 8 A Formula Request Office 2010 32bit
Novice
A Formula Request
 
Join Date: Oct 2014
Posts: 14
Planner18 will become famous soon enough
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 A Formula Request Windows 8 A Formula Request Office 2010 32bit
Novice
A Formula Request
 
Join Date: Oct 2014
Posts: 14
Planner18 will become famous soon enough
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 A Formula Request Windows 7 64bit A Formula Request Office 2010 32bit
Competent Performer
 
Join Date: Mar 2013
Posts: 160
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 A Formula Request Windows 8 A Formula Request Office 2010 32bit
Novice
A Formula Request
 
Join Date: Oct 2014
Posts: 14
Planner18 will become famous soon enough
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 A Formula Request Windows 7 64bit A Formula Request Office 2010 32bit
Competent Performer
 
Join Date: Mar 2013
Posts: 160
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 A Formula Request Windows 8 A Formula Request Office 2010 32bit
Novice
A Formula Request
 
Join Date: Oct 2014
Posts: 14
Planner18 will become famous soon enough
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 A Formula Request Windows 7 64bit A Formula Request Office 2010 32bit
Competent Performer
 
Join Date: Mar 2013
Posts: 160
Guloluseus is on a distinguished road
Default

Glad it worked

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
A Formula Request 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
A Formula Request 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 08:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2020, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2020 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft