Question about duration

Andrian_Andreev · 05-04-2019 12:29 PM

Hello probably a simple question but i can’t find the answer.
so here is my app:
It about completing jobs and traveling between two destinations before doing the job (lets just say the employe has to drive for x number of hours to get there). I don’t know how to make my duration work. So I have a column in witch they can input the type of job EX: travel, type of job1, type of job2, type of job3… and after that there is a second column with start and end. How do i calculate the duration between the start and the end? Do i need to have an unique value in order to be able to do it? or some sort of a ticket number? Also what is the formula that i need to input. Obviously its purpose for me to know how long it took to get to the customer is so that i can bill this. And also i need to know how long it took him to do the job in order to see if it was a normal job or a long. The duration for traveling will be the only one that I will be billing. The job it self will be billed by another way its just to have the statistic.

Thank you

Steve

Add a column called (e.g.) Time Spent to your table and set the app formula to:

IFS(
  ISNOTBLANK([end]),
  ([end] - [start])
)

Andrian_Andreev

thank you steve for the quick answer but perhaps i was not clear enough. once you chose your job type then you press start They are both a part of a different column (job type and start end). after the job is complete you close it but on my sheet the start and close end up in the same column. so what i need is that employee A open a job at 10 am and employe B open a job at 10:01 AM the sheet need to compute when they close that exact same job later with the duration of each. I hope it is not getting even more complicated for you.

Steve

Not entirely clear, but I think you mean the start tine and end tine are captured in separate rows? So employee might start a job, which is recorded by a row indicating that the job started and when, then later the employee finishes the job, which is recorded by a different row indicating that the job ended and when. Yes?

Andrian_Andreev

exactly they are in separated rows (BTW i can close them in the same even if its is done hours later??? but that is for another time hahaha). So lets say employe A starts the job in my row i get : ID(uniqueID), time, week number, type of job, and a start. When they close I get the same but a close on the same column as i got a start, + other information like what he did on the job but i think that is irrelevant.

Steve

How do you decide which end row goes with which start row? There need to be some way to tie them together. From there, calculating the duration is easy.

Andrian_Andreev

well thats maybe my big problem. When there is a job type there is always a ticket number witch has to be entered in the begging and in the end. (i would love to get the formula for that). but when i need to travel that is not a part of a ticket so i need to find a way to ID it differently each time but also that that number is repeated only once to close the same trip (travel). What i am trying to do is to get this app to do my job and that i can bill my customers almost automatically if the employees do their job correctly. And thank you very much for the time you are giving me.

Steve

Hmmm… The kind of app you want us possible, but I’m thinking you may need more help than we can provide here in the community if you want something quickly. Have you read this:

Andrian_Andreev

thanks can you tell me the formula for calculating duration based on my ticket number then? please

Steve

Okay, so what we need to calculate the duration is a way to match the end row with the start row. Will a given ticket number only ever occur twice in the table, once in the start row and once in the end row? Or can work on a ticket start and end multiple times?

Andrian_Andreev

I have a ticket number and a customer ID. The ticket number should (obvously if someone inputs the wrong one I cant control it) be occuring only once since every new customer demand generates a new ticket. I am not looking for the miraculous solution even if some jobs i dont get the duration it is ok. I know some employées wont even start the job. As i said earlyer this is more for a statistic perpose.

Andrian_Andreev

I just had an idea! Can we put a ‘‘if’’ formula for duration. Lets say it goes something like this:

if in column 1 i chose travel: calculate duration from unique ID in column 5, and if it is anything else than travel then calculate duration from unique ID in column 7

Steve

So no two rows will ever have the same ticket number, so there’s no way to match a start job row with a corresponding end job row?

Is “unique ID” the same thing as “ticket number”?

Andrian_Andreev

it is the same ticket number for a starting the job and ending the same job (that is if the employé starts the job) and no the uniquie ID is just to make sure there will never be a mistake on the part of my employées (that row is not even visible).

Steve

Is there something in the row that indicates that the row is a start row or and end row? Or is the start row merely the first row with the ticket number and the end row is the second row with the ticket number?

Andrian_Andreev

yes there is a column in wich it says either start or end. so first row its: employee name, start, ticked ID, customer number. next row employee name, end, same ticked ID, same customer number.

I dont want to use the customer number since a customer number can be repeated but not the ticket number.

Steve

Why do you want two rows (start & end) per ticket?

Andrian_Andreev

so that i can calculate the duration between when my employee got to the job and Left?

Steve

What’s the name of your table?

Andrian_Andreev

Fermeture

Steve

Okay. I’ve spent a considerable amount of time thinking about this problem, to the point of designing tables for you, but I think the easiest solution would be to use a single row per ticket, with columns like these:

Worker

Type: Name
Show?: ON
Suggested values: SORT(SELECT(Fermeture[Name], TRUE, TRUE))
Key: OFF
Editable?: ON
Require?: ON
Label: OFF
Searchable: ON

Ticket

Type: Text
Show?: ON
Valid If: ISBLANK(FILTER("Fermeture", AND(ISNOTBLANK([Ticket]), ([Ticket] = [_THISROW].[Ticket]))) - LIST([_THISROW]))
Key: OFF
Editable?: ON
Require?: ON
Label: ON
Searchable: ON

Job Type

Type: Text
Show?: ON
Key: OFF
Editable?: ON
Require?: ON
Label: OFF
Searchable: ON

Start When

Type: DateTime
Show?: ON
Initial value: NOW()
Key: OFF
Editable?: ON
Require?: ON
Label: OFF
Searchable: OFF

End When

Type: DateTime
Show?: ON
Valid If: ([_THIS] > [Start When])
Key: OFF
Editable?: ON
Require?: ON
Label: OFF
Searchable: OFF

ID

Type: Text
Show?: OFF
Key: ON
Initial value: UNIQUEID()
Editable?: FALSE
Require?: ON
Label: OFF
Searchable: OFF

Duration (virtual column)

Type: Duration
Show?: ON
App formula: IFS(ISNOTBLANK([End When]), ([End When] - [Start When]))
Key: OFF
Editable?: ON
Label: OFF
Searchable: OFF

Andrian_Andreev

i will create a new app with this infor you provided me i am curious how it will behave. for now i manged to do this formula but it is not working.

[time]

-MAX(SELECT(Fermeture[time],

AND([time] = [_THISROW].[time]

,[ticket #] = [_THISROW].[ticket #]

, [Statuts] = ‘‘start’’,

[tech name] = [_THISROW].[tech name])))

Steve

“Statuts” spelled correctly?

Steve

Why is this there? I wouldn’t think this needed.

Andrian_Andreev

[time]
-MAX(SELECT(Fermeture[time],
AND([time] = [_THISROW].[time]
,[ticket #] = [_THISROW].[ticket #]
, [Status] = ‘start’,
[tech name] = [_THISROW].[tech name])))

this is the working formula at last, my error was that i put two ‘’ instead of 1 '.(sry i dont know how to call this) thank you with everything you did steve

Andrian_Andreev

Hello steve,
i just re did all the tables as you mentioned on the post above from your app. I can’t see how the end time is supposed to get picked automaticaly. All i do it still asks me to input the end time manually.

Andrian_Andreev

please don`t leave me there

Steve

Gotta let me have the night off, bud.