Formula to set a duration (DateTimeRange)

Get Help
#1

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:

image
image987×190 13.2 KB

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

e.g.

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
#2

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

image
image850×681 20.2 KB

The formulas are as follows:

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

and

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

assuming that your database is called Meeting :slight_smile:

#3

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.

/Laurence

1 Like
#4

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

1 Like