Initial Value of Duration Type Column

AppSheet AppSheet Q&A
,
1

Hi I am looking to set the Initial Value of a duration column called [Time Allotted]. The initial value formula would be based on the number of people selected in the [Resource] column. 1 person selected, [Time Allotted]= 2 hours, 2 people selected, [Time Allotted]=1 hour, 3 people selected, [Time Allotted] 30 mins etc…

Could anyone suggest how to address this problem?

Many thanks.

Maurice.

2

MauriceWhelan:

3 people selected, [Time Allotted] 30 mins

Do you mean 40 minutes here? Which would be 120 minutes divided by number of people.

3

120/2^number of people-1? (halves for every person added)
If you are looking the count of people this COUNT([Resource]) should work so long as resource is itself a list. If Resource is some other column type might need a bit of a different formula.

4

Hi @Marc_Dillon

Turns out I was not provided with all information. It seems you are correct with your observation. The [Time Alloted] starting point is actually 8 Hours for 1 person to process 100 products. 2 people 4 hours, 3 people 2.66 hours etc…

What I need to do first is work out the total [Time Alloted] based on the [Quantity] of products e.g. if I have a quantity of 150 the first part of my formula, I think, needs to be 150/100 * 8 to get the total [Time Alloted] for 1 person. I then need to divide that number by the COUNT() of the [Resource] list to get the actual [Time Alloted] for this piece of work.

I hope this helps clarify a little.

5

I’m still thinking on if there is some smart way to do this better. But I can at least suggest for you to build the duration string up with concatenation, for now. Start by calculating time allotted in minutes.

time allotted minutes =

([Quantity]/100 * 8 * 60) / COUNT( [Resource] )

Then build the duration string for [Time Allotted]

“00”&> FLOOR( [time allotted minutes] / 60 ) &> “:” &> MOD( [time allotted minutes] , 60 ) &> “:00”

6

Thank you @Marc_Dillon

Should I just create [Time Alloted Minutes] as VC?

7

You can. Putting in another real column would be better performance-wise for the app though.

1 Like
8

This appears to be working as suggested @Marc_Dillon

Thank you.

1 Like
9

Hi @Marc_Dillon

The duration value is throwing a validation error in a scenario where [Quantity] = 50 and I add 2 [Resource]s from the EnumList. Image shows how the format of the initial value in this instance.

Could you advise? Many thanks.

3X_7_b_7b075aefd739944c76cc14e8a2fd395915a1b67b.png
3X_7_b_7b075aefd739944c76cc14e8a2fd395915a1b67b.png743×414 12.7 KB

10

The minutes section needs to have 2 digits, so “2:00:00”. You probably need to add some logic into the expression to handle this and add an extrA “0” when the minutes value is less than 10.

2 Likes
11

Hi

I have tried to solve this issue with
IFS([Department]=“POLE”,
CONCATENATE(
“00”,
FLOOR( [Time Alloted Minutes] / 60 ),
“:” ,
IF(MINUTE([Time Alloted])<10,CONCATENATE(“0”,MOD( [Time Alloted Minutes] , 60 )),
MOD( [Time Alloted Minutes] , 60 )),
“:00”
))

which seems to work if the minutes value is less than 10 but if it is greater than 10 I am getting 3:026:00

Am I on the right track?

Many thanks.

12

I think for a duration to be valid it had to be in this format:

hhh:mm:ss

hh:mm:ss I believe appsheet interprets as a time not a duration

13

Perhaps something like:

CONCATENATE(
  FLOOR([minutes] / 60),
  ":",
  RIGHT(("00" & MOD([minutes], 60)), 2),
  ":00"
)
2 Likes
14

Thanks so much @Steve. Any test i have done seems to be working perfectly.

1 Like