Workflow Calculaitons ISO-8601 numeric representation of the day of the week

Afternoon,
I’ve built a simple datediff calculation between two dates. Works as it should. I now need to build something similar but it needs to count every Monday, Wednesdays, and Fridays, between the given dates. I see the ISO-8601 numeric for each day. I can’t figure out how to write the formula! I’ve read and tried and tried but nothing. Any help would be greatly appreciated.

This is really just a programming question, not a SuiteCRM question. Maybe you could ask this on StackOverflow?

I think you will probably need to use a subtraction of days to see the difference in days between the dates. Dividing that by 7 you get an approximate number of weeks.

But then you will have to add some logic to take into account the weekday of the first date, and the weekday of the second.

For example, if your dates are 12th February and 30th April. The difference in days is 78 days, which gives approximately 11 weeks.

But now to decide precisely how many Mondays were in that period (10? 11? 12?), you will have to add more logic, looking at the weekday of the 12th february and at the weekday of 30th april.

thanks for your reply. Based on the documentation provided by Suite, there are functions “for day”, ISO-8601 being one of them as “N”. I just lack the experience how to integrate that into a formula on the calculated workflow.

using {now(N)} it will add the correlating number for the day of the week into the specified field. I did this today so it added a “3” which represents “Wednesday”. My guess is it would be something like this but only counting days 1, 3, 5 (Monday, Wed, Friday) between two dates.

a pgr said, datediff could be used, but it is inaccurate for the mentioned reasons, and workflows/formulas are not very suited to do loops.

a php example that calculates these counts:

function daycount($day, $date1, $date2, $counter)
{
    if($date1 >= $date2)
    {
        return $counter;
    }
    else
    {
        return daycount($day, strtotime("next ".$day,$date1),$date2, ++$counter);
    }
}
echo daycount("monday", strtotime("10.04.2020"), strtotime("25.04.2020"), 0);

(copied & modified from here: https://stackoverflow.com/questions/1653891/how-to-find-number-of-mondays-or-tuesdays-between-two-dates)

1 Like

thanks for you reply. I don’t think the calculated workflows can handle your php example. I may need to do this count in stages. One hidden field to hold the count for just a monday. A second hidden field to hold the count for wed and a third for friday. Lastly, the target field to add those three fields together to give me a total. I’m trying to avoid writing any code to manage this but alas, we may have to.

If you want to have accurate numbers and use only workflows, you need to set them up with the attribute “repeated runs” checked.

Then you will need

  • a start date,
  • an end date,
  • and an integer field per weekday that is of interest (default value = 0)
  • your workflows have to run multiple times per record (until the start date equals the end date)
    • if the start date equals your desired weekday, then increase its counter by 1
    • after this if-statement, increase the start date by 1 day

this should work (at least somehow like this), but it is highly inefficient. It might slow down your system a lot, while the suggested php-code does the same job fast and reliable.

E: and no, you can’t inject php-code into workflows.

thank you. I ended up building a workflow based on what PGR suggested. Total days between dates, divide by 7 and them multiply by however many days of the week I’m trying to count. ended up including “add 1” at the end. Oddly it is pretty accurate. It will do for now.