Complex Time Calculations - difference between created dates

grant53396

Hi,

Having an ongoing battle here with trying to work around the system generated "Created" date/time stamp. I want my guys to fill in a form when they start and stop doing work on a project in our production line, and then evaluate the time spent on each serial number job. My grid of sample responses looks like below:

You will see that there are multiple entries for 402860 serial number. The guy working in 1st fix electrical, Grant, starts this, then goes on break (which means he stops, basically), and then starts again, and finally stops. I want to know how long he spent on this in minutes.

You can see from the image that i can manually subtract one time from the other, multiply by 24 (which gives me the hours.minutes decimal) and then multiply by 60, which gives me the time in minutes spent on the job.

What i cant get my head round is how to do the sumifs (if thats the right function) to calculate the difference between start and stop for each process... is this even possible? I have started with the below sumifs, but the answer is "0", and cannot work out why? Is there another function?

Be glad of any assistance on this!! Happy to share sheets etc. if necessary...

@Paul Newcome @Mark Cronk - you both have been helpful so far, and seem to understand the time element well - this maybe something you understand easily??

Kelly Moore

Hello @grant53396

I have a process at one facility where I do exactly what you're trying to do using Created field, although @Paul Newcome and @Mark Cronk will certainly offer more eloquent solutions.

I use 2 forms, start and stop. A QR code accesses the forms- we've color coded the QR codes. With >400 temporary employees, we do not build individual start/ stop forms per employee. My "Start Job" form asks for Employee# and Job#, and various sub-task specifics. It has a hidden checkbox field to indicate it is the START. My "End Job" form also has Employee# and Job# and the same subtask fields.

On each row I extract the Date from the Created time, then convert to military time (kudos to one of Paul's old post). On every Stop Job row I:

(1st) connect the Start-stops together using an Index/Collect looking at the Max Start time that is less than this Stop time. (see below) (We ignore breaks - only track Job start/stops)

(2nd) do the subtraction to get the elapsed time

On a separate sheet a list pulls the SUMIFS per employee. A nightly report emails this payroll. Jobs # are tracked Weekly on a different sheet.

=IF([Start Time Only]@row = 0, INDEX(COLLECT([Start Time Decimal]:[Start Time Decimal], [Start Time Decimal]:[Start Time Decimal], MAX([Start Time Decimal]:[Start Time Decimal]) <= [End Time Decimal]@row, EmployeeNo:EmployeeNo, @cell = EmployeeNo@row, Date:Date, @cell = Date@row, [Job No]:[Job No], [Job No]@row, Activity:Activity, @cell = Activity@row), 1))

I hope this helps,

Kelly

grant53396

Thanks @KDM - most of this makes sense, but am really struggling on the "...extract the date from the created time, then convert to military time..." - could you explain how you do these?

Paul Newcome

I would suggest starting with two more columns. AN autonumber column that doesn't require any special formatting and then a text/number column (called "RowNum") with the following column formula:

=MATCH(Autonumber@row, Autonumber:Autonumber, 0)

This will replicate your row numbers so we can leverage them in your formulas.

You can then use

=SUMIFS([Hours Difference]:[Hours Difference], RowNum:RowNum, @cell <= RowNum@row, [Serial Number]:[Serial Number], [Serial Number]@row)

This will give you a running total of your [Hours Difference] column as you progress down the sheet for each of the different serial numbers.

Kelly Moore

Hey @grant53396

First, consider Paul's solution. It would be more straight forward.

Because of reporting requirements of my users, I split my date stamp into a date columns and a time column. Now using the recent Record Date Automation, I trigger off a change in created column for the date. For Time I use =IF([Start Time Only]@row = 1, IF(LEN(Created@row) = 17, RIGHT(Created@row, 8), RIGHT(Created@row, 7)))

Paul has many posts on converting time. You can find his compendium here. I used one of his formulas.

I converted both my start and end times into military format for simpler math

=IF([Start Time Only]@row = 1, (VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) + IF(CONTAINS("p", [Start Time]@row), IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Start Time]@row, FIND(":", [Start Time]@row) - 1)) = 12, -12))) + (VALUE(MID([Start Time]@row, FIND(":", [Start Time]@row) + 1, 2)) / 60))

With >800-3200 entries in the sheet per day, I move the Stop rows, where I have already pulled the start data into this row, to one sheet to archive. I move Start rows to another sheet where eventually they are manually deleted. That's my process- not pretty, but it works.😉

Kelly

grant53396

Thanks @Paul Newcome - much appreciated. i have set up the columns as suggested, but this is coming up as unparseable? Again, probably me being dumb!!

My other comment is that the data in Hours Difference is only sample data at the moment, but will need to work this out within the same formula, if that makes sense... the data in here is only a calculation that i had done already...

unless there is a way that i can extract the hours only out of the automated column, and i can make this work easier??

Sorry to be a pain!!

Paul Newcome

The error itself... You may want to check your data within the ranges to make sure that error isn't present anywhere else that is being referenced. That one shouldn't pull through, but the formula itself does look fine. Also try changing [Hours Difference] to [Hours difference] (lowercase "d") to match the actual column name.

In your original post it looks like you have a formula in the Hours Difference column already. Is that not working?