Formula to set a duration (DateTimeRange)

Hello fellow friendly fibery folk,

In short - is it possible to easily manipulate date times using formulas, e.g. set / add / remove units of time.

Pain: “Setting a duration involves lots of clicks”
Idea: “Create a button that does some the hard work”

E.g. Duration = 30 minutes, Duration = 1 hour

So for a DURATION field, I need to set a sensible DateTimeRange.

I got as far as:

DateTimeRange(Now(), Now())

Which does what you’d expect:

However, what I’d actually like to do is!

  1. Get the current date
  2. Extract the current time
  3. Round the time down to a nearest interval (e.g. 15 minutes)
  4. Create a time range using the rounded down internal as the start
  5. Send the end range to the start plus X minutes


current datetime = 2020-01-13-20-23-12
rounded to nearest interval = 2020-01-13-20-15-00

start = rounded to nearest interval
end = start + 30 minutes

Any ideas much appreciated :+1:

1 Like

Create an integer field called ‘Surplus’.
Then make a button with the following actions:

The formulas are as follows:

Minute(Now()) - (RoundDown(Minute(Now()) / 15,0) * 15)


DateTimeRange(Now() - Minutes([Step 1 Meeting].Surplus),Now() - Minutes([Step 1 Meeting].Surplus) + Minutes(30))

assuming that your database is called Meeting :slight_smile:

Hi Chris,

Apologies for my delay replying to this, I’ve been mulling it over!

I am going to try and implement something along these lines.

The main point I’ve been thinking about is this. Is there any way to have a locally scoped variable (e.g. within the formula)? It seems a bit brittle to add additional fields for the purpose of these calculations, especially as there no easy way to understand what those fields are used for (e.g. if they are being used at all).

My fear is creating something that I won’t understand in 18 months time.

A locally scoped variable would keep everything in one place.

Thanks again for your suggestion it’s helpful.


1 Like

It’s a fair point, and you’re not the first to recognise a need:

1 Like