Working days calc

[troyhoskison]

Hi,

I have a CDS table that has a field in that is the Customer due date.

From this I need to work out the internal due date, which is 2 days before the customer due date. However i need 2 working days not two actual days.

• A calculated field it limited to only actual days, not working days.
• Business rules have the same limitations
• Power automate might work, but it only runs once every 5 minutes, so not useful in a production environment

Any thoughts on how I sort this problem out?

The second part is; once I have the internal due date I need the start date, which is the internal due date minus the lead time.

Thanks

Troy

[mogulman]

I posted earlier but you may want to write an Azure Function.  You can call it from Power Automate.  I had to calculate Business Days from a certain date with both adding and subtracting days.  I had to exclude Bank Holidays.  I created a table with Bank Holidays since they are not always consistent.   I also had to calculate other dates like Due Date, Grace 15, Grace 30, Calendar days.  An Azure function is really powerful.  Example code

        public static DateTime CalcDate(DateTime date, string dueDateOffset, Dictionary<DateTime, int> hd)
        {
            int dCnt = 0;
            DateTime calcDate = new DateTime(1900, 1, 1);
            string[] parts = dueDateOffset.Split('|');
            if (parts[0] == "CD")
            {
                if (Int32.TryParse(parts[1], out dCnt))
                {
                    calcDate = date.AddDays(dCnt);
                }
                else
                {
                    calcDate = date;
                }
            }
            if (parts[0] == "BD")
            {
                int idx = 0;
                if (Int32.TryParse(parts[1], out dCnt))
                {
                    if (dCnt < 0)
                    {
                        idx = -1;
                    }
                    else
                    {
                        idx = 1;
                    }
                    dCnt = Math.Abs(dCnt);
                    while (dCnt > 0)
                    {
                        date = date.AddDays(idx);
                        if (!(date.DayOfWeek == System.DayOfWeek.Saturday || date.DayOfWeek == System.DayOfWeek.Sunday || hd.ContainsKey(date)))
                        {
                            dCnt--;
                        }
                    }
                    calcDate = date;
                }
                else
                {
                    calcDate = date;
                }
            }
            if (parts[0] == "DD")  //Due Date: DD|1 is 1st of next month, DD|2 is 1st 2 months from now.
            {
                if (Int32.TryParse(parts[1], out dCnt))
                {
                    date = date.AddMonths(dCnt);
                    date = new DateTime(date.Year, date.Month, 1);
                    calcDate = date;
                }
            }
            if (parts[0] == "G15")  //Grace 15: G15 is 15th of next month.
            {
                date = date.AddMonths(1);
                date = new DateTime(date.Year, date.Month, 15);
                calcDate = date;
            }
            if (parts[0] == "G30")  //Grace 30: G30 is last day of next month.
            {
                date = date.AddMonths(2);
                date = new DateTime(date.Year, date.Month, 1);
                date = date.AddDays(-1);
                calcDate = date;
            }
            return calcDate;
        }

[troyhoskison]

that looks very scary and way out of my comfort zone. I don't even know how to create an Azure function.