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

  • And which error did you get? Or I mean what is the problem you are facing? – Sergej Utko Feb 18 '15 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% – Darko Todorovski Feb 18 '15 at 10:52
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

| improve this answer | |
  • Can you please elaborate why you have *100 ? Its confusing a bit. Thanks – Darko Todorovski Feb 18 '15 at 14:31
  • This is a percent calculation to get a human friendly percent digit like 25 and not 0.25 – Sergej Utko Feb 18 '15 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 Jun 24 '19 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.