Add duration to datetime but skip certain time windows

AppSheet AppSheet Q&A
,
1

Hi all

Is it possible to add a duration to a datetime but skip certain days and time windows?

Example

[datetime] = 06.10.2022 15:00

[duration] = 12:00

Rule: skip the whole weekend and weekday times between 17:00 and 08:00.

Result should be: [datetime] + [duration] = 10.10.2022 10:00

Is there a formula for this?

Thanks in advance.

Adrian

2

Not possible.

1 Like
3

I’m not clear on your use case, but you might try using a combination of WEEKDAY(), INDEX(), and/or SWITCH() to achieve your production days/hours window if that’s what you’re seeking.

The formula could leverage a table where you can re/set the durations for each DOW. That way you’d only need to update the table and not the formula.

And you could create a form to allow select users to modify the table under controlled parameters – to ensure data integrity.

1 Like
4

Yes but you’ve got to code it for every eventuality.

  1. Starts on a Sat
  2. Starts on a Sun
  3. AND(Starts on a weekday >= 1700, Starts on a weekday <= 0800)
  4. AND(Starts on a weekday < 1700, Starts on a weekday > 0800)
  5. Ends on a Sat
  6. Ends on a Sun
  7. AND(Ends on a weekday >= 1700, Ends on a weekday <= 0800)
  8. AND(Ends on a weekday < 1700, Ends on a weekday > 0800)
  9. Stradles one or more weekends
  10. Stradles one or more week nights

Roughly the formula would be TOTAL Duration - Item 1 above - Item 2 above - Item 3 above …

Not quick, easy or simple to test. But doable. I’ve done similar many times before.

Simon@1minManager.com

2 Likes
5

True.

One way I can think of to simplify it is to think of time as a continuum instead of daily segmented blocks. This would make it easier to add durations to cover END points, since none of the START/END points repeat in a given week.

For example: Using hours, there is only one timespan for Monday 5PM to Tuesday 2A.

  • Start At: 41st hour
  • Duration: 9 hours
  • End At: 50th hour (41+9)
  • Start/Stop Timespan (hours): 41-50

JJ_TechSupport_3-1665077916094.png
JJ_TechSupport_3-1665077916094.png1116×134 5.19 KB

Handling Wraparounds: If the goal is to specify production windows, then only SAT to SUN is the area where we would need to specify two segments of time.

2 Likes
6

Thanks for the replies.

No matter how hard I try, I can’t wrap my head around that. I mark this as the solution, others probably can work with this information. This is a bit too complicated for me.