2

I am trying to develop a formula that will give me a progress bar with images in the campaigns

The idea that i want to achieve is that I have days from the start of the campaign till the end of the campaign(field Days_from_Start_to_End_Date__c). And with those numbers i want to see the progress bar changing.

For example if the StartDate = Today() that means progress bar should point out 0%, If EndDate = Today() that means progress bar should point to 100%.

Now the catchy part is i want somehow the number of days to be divided in 5 so if i have 20 days for all campaign. I want to divide it 20/5. So the formula should point out something like this.

  • from 0 till 4 days 0%

  • from 5 days till 8 20%

  • from 9 till 12 40%

  • from 13 till 16 60%

  • from 17 till 20 80%

  • 20+ 100%

Here is where i am now with my code.

IF( StartDate = Today(), IMAGE("/servlet/servlet.FileDownload?file=01520000002aVsZ", "0%"),
    IF (  Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5, IMAGE("/servlet/servlet.FileDownload?file=01520000002aVxZ", "20%"),
    IF ( Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5, IMAGE("/servlet/servlet.FileDownload?file=01520000002aVxe", "40%"),
    IF ( Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5, IMAGE("/servlet/servlet.FileDownload?file=01520000002aVxj", "60%"),
    IF ( Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5, IMAGE("/servlet/servlet.FileDownload?file=01520000002aVy3", "80%"),
IF ( Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5 + Days_from_Start_to_End_Date__c / 5, IMAGE("/servlet/servlet.FileDownload?file=01520000002aVy3", "100%"),
          "ERROR"))))))

Also in the code abowe you can see the pat of the different images from 0% till 100%.

I really hope i was clear with the explanation if not please let me know.

Please advise,

Darko

2
  • And which error did you get? Or I mean what is the problem you are facing? Commented Feb 18, 2015 at 10:49
  • The problem that i am facing is i can't figure a way out to do this. I've tried everything and I am not able to find a way to make the the formula does what i want. See below from 0 till 4 days 0% from 5 days till 8 20% from 9 till 12 40% from 13 till 16 60% from 17 till 20 80% 20+ 100% Commented Feb 18, 2015 at 10:52

2 Answers 2

2

I've just experimented with some fields on my org. The following formula should work.

I use here following fields:

  • Start__c
  • End__c
  • NumDays__c: Formula (End__c - Start__c)
  • Today__c: just a Date field for more comfortable testing purpose

The main formula:

IF( (((Today__c - Start__c)/ NumDays__c)*100) == 0, IMAGE('/img/samples/stars_000.gif', '0%'), 
(IF((((Today__c - Start__c)/ NumDays__c)*100) > 0 && (((Today__c - Start__c)/ NumDays__c)*100) <= 20,  IMAGE('/img/samples/stars_100.gif', '20%'),
(IF((((Today__c - Start__c)/ NumDays__c)*100) > 20 && (((Today__c - Start__c)/ NumDays__c)*100) <= 40,  IMAGE('/img/samples/stars_200.gif', '40%'),
(IF((((Today__c - Start__c)/ NumDays__c)*100) > 40 && (((Today__c - Start__c)/ NumDays__c)*100) <= 60,  IMAGE('/img/samples/stars_300.gif', '60%'),
(IF((((Today__c - Start__c)/ NumDays__c)*100) > 60 && (((Today__c - Start__c)/ NumDays__c)*100) <= 80,  IMAGE('/img/samples/stars_400.gif', '80%'),
(IF((((Today__c - Start__c)/ NumDays__c)*100) > 80,  IMAGE('/img/samples/stars_500.gif', '100%'),'100%')))))))) )) )

And here is a result:

enter image description here

3
  • Can you please elaborate why you have *100 ? Its confusing a bit. Thanks Commented Feb 18, 2015 at 14:31
  • This is a percent calculation to get a human friendly percent digit like 25 and not 0.25 Commented Feb 18, 2015 at 14:34
  • It does not support width percentage anymore. The format of IMAGE is IMAGE(image_url, alternate_text, height, width) currently.
    – Cray Kao
    Commented Jun 24, 2019 at 9:12
2

Something like this should do the trick:

IF(

(TODAY()-StartDate)/(EndDate-StartDate) < 0.2, IMAGE1,

IF((TODAY()-StartDate)/(EndDate-StartDate) < 0.4, IMAGE2,

IF((TODAY()-StartDate)/(EndDate-StartDate) < 0.6, IMAGE3,

IF((TODAY()-StartDate)/(EndDate-StartDate) < 0.8, IMAGE4, IMAGE5)

)))

You probably also want to capture the situation when TODAY() < StartDate or TODAY() > EndDate, but this should get you started.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .