Manipulating Dates and Times

[Dave B]

Does anyone have experience of manipulating dates and times, beyond the basic functions provided? For example, I want to be able to do things like:

- round a datetime to the nearest 15 minutes
- check to see whether a datetime is before a certain time of day (for example "is $startwork < '06:00' ", or "is this time after midnight")

Thank you

[shalmaxb]

A lot possible with formula: https://docs.espocrm.com/administrat...mula/#datetime

[Dave B]

Thank you - yes, but for example, how would you round a time to the nearest 15 minutes using those formulae?

[shalmaxb]

Out of the box, there is not a "round" function in datetime formulas, but perhaps it could be possible to develop a custom formula for that: https://docs.espocrm.com/development...on-in-formula/

[esforim]

There is a Round formula... I guess you can try to do mix formula and it might work;
https://docs.espocrm.com/user-guide/...essions/#round

However Round is usually round up to 0 to down to 0... rounding it up to 15 you would need to create your own function somehow... perhaps through math instead.

For example (written with no test but purely GUI only knowledge here):


For time formula you have this: datetime\minute(VALUE, [TIMEZONE])
For Round formula you have this: round: (value,2)

Let try to mix it, I doubt you trying to manipulate current time? Perhaps you trying to do Billing hours round to 15 minutes?

So... I would try this inefficient (but potentially working formula):

number\ceil(bills)-5

https://docs.espocrm.com/administration/formula/#numberceil

This would only manipulate it to nearest 5 minutes though, I can't think of a maths method to get it to 15 minutes.

[shalmaxb]

i do not think, this will work. The round function is for number values and as far as I know, datetime in espoCRM is not a number. But maybe I am wrong.

[Dave B]

You have prompted me to think like this:
Extract the minutes as a number, divide by 15, round to zero decimals, and multiply by 15. If the answer is 60, reset to zero and add 1 to the hour. Will code it up later and give it a go

[esforim]

Date and time should be a number shalmaxb, it not an exact numbersbut there is a set standard numbers that determine the date:time value. It just a matter of how easy or difficult it is to manipulate the number. The Events +duration feature in CRM is one such way that numbers is manipulated and calculate.

I know this from my Excel experience, computer is quite magic at times.

---

Dave B B
Good luck on your journey! Sound like a light sparked in you, but without trial and error this is all theory at the moment.

[shalmaxb]

This thread is about date formatting, perhaps it helps for your purpose: https://forum.espocrm.com/forum/gene...format-quartal

[esforim]

The possibility is limitless! If you have math & code skill that is.