Round up/down datetime field with BPM or Formula

hello Pete

below is an idea of rounding down to the hour by retrieving hours digits from the datetime field value via conversion to string following conversion to a number - that may help to build back the necessary DateTime value by concatenating datetime components (day, month, year, hour, 00 minutes)
I hope this helps.

however, I'd note that having no coding skills I would configure calculating DateTime Start with Logic Builder tool for Sugar - with a few clicks and no-code - on Task is created/assigned (or whatever other logic triggers Datetime Start calculation) and deploy configuring to SUgar via Module Loader

Thanks for the suggestion Dmytro. 

I was going down this path, however the issue I came across was how to get the deconstructed elements back into a datetime format so that the BPM Business Hours logic could work on it as it only works on datetime fields.

Oh, seems that I'm also unable to find a way of getting a DateTime field value from the number of datetime components with SugarLogic formulas of CalcFields... 
again, I would use the Logic Builder to solve the whole task with no-coding in Sugar

however, some of my experiments with Business Center and Business Rules have led to the results that looks fruitful

I used the rounded down hour number for Task createdate and set up this Business Rule:

Where MyBusinessCenter1 is that Business Center:

If a Task is created before noon, that should BPM sets DateTimeStart to the datetime in 4 business hours (according to MyBusinessCenter1)  from createdate. For tasks created after noon time, the DateTimeStart should be at 14:00 of the upcoming working day  (according to MyBusinessCenter1)

Note that, Business Center timezone, my real time zone and the time zone of the user profile in Sugar are equal in my test

Please let me know if the trick worked
If not, I'll try configure your task's solution with Logic Builder 

Thanks again Dmytro.

What you have suggested does go a long way to getting to what I am trying to achieve and I had tried this previously too. It does indeed work perfectly for being able to get the overall offset correct but it is the rounding out of the minutes that I cannot figure out an answer for that  not as elegant as I am hoping to be able to achieve.

Where this is still not quite ideal is where we have an example situation of make credit card payments. The deadline to process these by the Bank is 4PM.

Therefore, anything raised before 12:00 would have a Due Date of 16:00 (4bh) that day and anything after would be 16:00 the next business day.

Using the above works great for doing the overall offset, however if a task is raised at 11:42 the Business logic would add 4 Business Hours and set the due date for 15:42.

I have been trying to apply similar logic to the minutes

However it keeps rounding it up to the nearest 15.

I don't know about BPM (I am still working on Pro an we don't have that) but using toString on a date in sugar logic is a bit dangerous as it returns the format that the user has chosen and the date translated to the user's time zone. 

For example a DB date/time stamp of

2020-04-17 18:21:43

will return

toString($date_entered) = 04/17/2020 01:30pm

or

toString($date_entered) = 2020-04-17 13.30

depending on my profile settings.

If you don't care about that and you're sure you will stick to one format you can build some logic based on hour and minutes.

For example if your DB datetime = 2020-04-17 18:21:43 and your user is set to that same format then

whole date =  toString($date_entered)
2020-04-17 13.30

date  = subStr(toString($date_entered),0,10)
date = 2020-04-17

hour =  subStr(toString($date_entered),12,2)
hour = 13

minute = subStr(toString($date_entered),15,2)
minute = 30

then you can build a series of ifElse statements to check if it's, for example, past 08:59 make it the next day at 14:00

if the hour is greater than 8:

greaterThan(number(subStr(toString($date_entered),11,2)),8)

then return "14.00"

else (the hour is less than 8)

if the minutes are more than 29

greaterThan(number(subStr(toString($date_entered),15,2)),29)

then round up to the next hour by converting the hour to a number and adding one

number(subStr(toString($date_entered),12,2))+1

you will need to check if the result is greater than 24 (you'll need to do more work if it is)

greaterThan(number(subStr(toString($date_entered),12,2))+1, 24)

.... etc...

you get the gist.

I'll be honest with you, if I was doing this I would certainly not do it with sugar logic, I'd rather do it with a logic hook and even then it would be a bit of a pain.

If you don't want to do any programming Dmytro Chupylka's Logic Builder (which is a third party tool he sells that generates it's own logic code, not a sugar logic hook) may be one way to go. I can't speak to Logic Builder specifically, I gave it only a cursory look since I prefer to develop my own logic hooks within the sugar framework.

FrancescaS

I don't know about BPM (I am still working on Pro an we don't have that) but using toString on a date in sugar logic is a bit dangerous as it returns the format that the user has chosen and the date translated to the user's time zone. 

For example a DB date/time stamp of

2020-04-17 18:21:43

will return

toString($date_entered) = 04/17/2020 01:30pm

or

toString($date_entered) = 2020-04-17 13.30

depending on my profile settings.

If you don't care about that and you're sure you will stick to one format you can build some logic based on hour and minutes.

For example if your DB datetime = 2020-04-17 18:21:43 and your user is set to that same format then

whole date =  toString($date_entered)
2020-04-17 13.30

date  = subStr(toString($date_entered),0,10)
date = 2020-04-17

hour =  subStr(toString($date_entered),12,2)
hour = 13

minute = subStr(toString($date_entered),15,2)
minute = 30

then you can build a series of ifElse statements to check if it's, for example, past 08:59 make it the next day at 14:00

if the hour is greater than 8:

greaterThan(number(subStr(toString($date_entered),11,2)),8)

then return "14.00"

else (the hour is less than 8)

if the minutes are more than 29

greaterThan(number(subStr(toString($date_entered),15,2)),29)

then round up to the next hour by converting the hour to a number and adding one

number(subStr(toString($date_entered),12,2))+1

you will need to check if the result is greater than 24 (you'll need to do more work if it is)

greaterThan(number(subStr(toString($date_entered),12,2))+1, 24)

.... etc...

you get the gist.

I'll be honest with you, if I was doing this I would certainly not do it with sugar logic, I'd rather do it with a logic hook and even then it would be a bit of a pain.

If you don't want to do any programming Dmytro Chupylka's Logic Builder (which is a third party tool he sells that generates it's own logic code, not a sugar logic hook) may be one way to go. I can't speak to Logic Builder specifically, I gave it only a cursory look since I prefer to develop my own logic hooks within the sugar framework.

FrancescaS

Hi  Francesca,

Let me highlight the benefits of using BPM for this particular task

Sugar BPM allows to run Business Rules , the ones allow to use Time Span for adding business hours according to the Business Center availability time

It means that if e.g. the request is submitted on Sunday (dayoff) , and the office (Business Center) opens on Tuesday at 1pm and closes at 6 pm, then adding 2 business hours will give us the datetime of "Tuesday 3pm" 


Being implemented in the out-of-the-box Sugar, it helps to save a lot of time and/or money on custom logic hook developments, because all the implementation of available date calculations in the logic hook could be easily avoided, and I'm not saying here about learning the code development

I'm trying to highlight these advantages of the BPM, because Sugar Pro is not in the Sugar pricelist anymore, and we believe that very soon every subscriber will be able to benefit from SugarBPM functionality on-prem as well as in Sugar Cloud

So our the task for this case is to fill out the gap in the fruitful BMP mechanic by, I suppose, overriding the time to 16:00 and perhaps that could be done with Logic Builder configuring and then included into the SugarBPM

Regarding the tool - from Sugar Admin perspective, Logic Builder is Sugar configuring tool, from a technical perspective Logic Builder is nothing else but a Sugar custom code generator - it delivers no-code the customizations that developers got used to delivering

SugarPro customers now have access to SugarEnt, I've just not had the time to do the conversion on our systems yet.

Thank you for pointing out the advantages, I will have to spend some time learning how to work with BPM.

F.