Solved: Initial Value of Duration Type Column

MauriceWhelan · 08-31-2020 07:19 AM

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.

Steve

Perhaps something like:

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

View solution in original post

Marc_Dillon

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

MauriceWhelan

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.

Marc_Dillon

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”

MauriceWhelan

Thank you @Marc_Dillon

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

Marc_Dillon

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

MauriceWhelan

This appears to be working as suggested @Marc_Dillon

Thank you.

MauriceWhelan

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.

Marc_Dillon

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.

MauriceWhelan

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.

1minManager

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

Steve

Perhaps something like:

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

MauriceWhelan

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

Austin

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.