9 Replies Latest reply: May 15, 2014 9:55 AM by Lynette _

# Time Span

Hi,

I'm in need of some help.

I have the following report. I need a calculated field for elapsed time (End - Start time)

How can I achieve this? I looked at time span although it doesn't seem applicable. I would like the result in minutes.

[font="courier"]Assembly     Qty     Start                   End               B/U #     Assembler

DC2847900     50     2006-03-07 8:16:08 AM     2006-03-07 11:54:42 AM     115127     Kim

DC2847900     50     2006-03-07 8:19:42 AM     2006-03-07 11:55:42 AM     115127     Chris

J02602-6     99     2006-03-07 8:27:17 AM     2006-03-07 12:00:00 PM     112783     Kathy

J02602-6     99     2006-03-07 8:27:42 AM     2006-03-07 12:00:00 PM     112783     Sue  /font[/quote]Thanks, in advance, for your help

• ###### Time Span

Nice solution Todd.

I can never remember the number of seconds in a day  - maybe I would if I needed it often enough - so I tend to make the calculation in the 'long' form. And of course once you are happy with the concept and the result unless you need the separate fields for some reason the whole calculation can be set up as a single field.

((-[Start Time])(2460*60))/60

Now, if you add some descriptive text to the formula for ease of remembering what it does later (or if someone else may have to understand it at some point) you could also truncate it to this;

(-[Start Time])(2460)

which will give the same answer in a field defined as Numeric and 'General' as Todd explained.

Two observations related to the sample report. (Probably most relevant to future forum browsers...)

Firstly you MUST INCLUDE the AM or PM indicators in the fields if the times are in 12hour format or the calculations will produce unexpected results.

Secondly the sample has no leading zero's before the hours in the START column and the temptation would be to create a 21 char wide field whereas the END column is 22 chars. Both fields would need to be 22 chars I would imagine.

I hope this is of some use.

Grant

Edits due to failure of proof reading! (Twice ...    )

[size="1"][ March 08, 2006, 01:26 PM: Message edited by: Grant Perkins ][/size]

• ###### Time Span

Thanks to both of you for solutions.

It works great!

I have another issue.

The solution you provided allows me to determine assembly times of a particular item.

What I need to do is consider multiple assemblers producing the same item.

As extracted, each assembler may be clocked in to a common Assembly,B/U# and Qty.

Find all records that are common with the three fields listed.

In those instances find total sum of hours while maintaining the single record quantity.

Does this make any sense? I hope so.

Please let me know if you have ideas.

Thanks,

• ###### Time Span

Find all records that are common with the three fields listed.

In those instances find total sum of hours while maintaining the single record quantity.

Does this make any sense? I hope so.

Please let me know if you have ideas.

Thanks,

That sounds like a job for a Monarch summary. Have you already looked into that?

If so was there a reason that it did not seem to work for you?

Grant

• ###### Time Span

Hi Grant,

Using Summary Count function worked.

Exported, Lookup and calculate results.

Thanks.

I need a suggestion for another issue.

Sample of present Monarch Table below.

I need to do the following:

If Start time is 12:00:00 AM then needs to be 8:00:00 AM

If End time is 11:59:59 PM then needs to be

4:30:00 PM

All other times stay the same.

I tried using the IF function but received operand errors.

The time has both the Date and Time.....the Date always remains the same....just the time needs to change.

Ideas?

Thanks again,

[font="courier"]Assembly     Qty     UNIT TIME     Start     End     Assembler     ELAPSED TIME     B/U #     MINUTES     COMMONS

157-089     40     1.05     2006-03-10 2:02:02 PM     2006-03-10 2:43:37 PM     Darcie     2495          42     1

157-089     40     2.18     2006-03-10 10:32:52 AM     2006-03-10 12:00:00 PM     Leah     5228     115698     87     1

157-089     40     17.25     2006-03-10 12:30:00 PM     2006-03-10 11:59:59 PM     Leah     41399     115698     690     1

157-089     40     17.93     2006-03-11     2006-03-11 11:57:10 AM     Leah     43030     115698     717     1

157-092     40     0.65     2006-03-10 3:39:02 PM     2006-03-10 4:05:13 PM     Brian     1571          26     1

/font[/quote]

• ###### Time Span

Grant,

After I looked at the reply I realized I never explained what I'm trying to do.

When an employee forgets to clock out of a project the software presumes they worked through the night. It clocks them out at 11:59:59 PM and clocks them in at 12:00:00 AM.

Our work day is 8:00 am to 4:30 pm.

Prior to exporting the data I need to correct any clock-in or out errors.

Thanks again

• ###### Time Span

When an employee forgets to clock out of a project the software presumes they worked through the night. It clocks them out at 11:59:59 PM and clocks them in at 12:00:00 AM.

/b[/quote]Hmm.

I have heard of a few people who made really good money and gained company awards for commitment by forgetting to clock off ...

But in this cas I guess their productivity would not look too good!

Do you realise you may be upsetting some of the workers Brad?

Grant

PS. Thanks for the explanation. I am very fond of the concept of looking for a by-product of model which can highlight potential error situations and simplify checking as well as models where such checks are the prime purpose.

• ###### Time Span

You need something like this.  Note the curly braces around the specification of time values.  This is also required when specifying dates as constants.

[font="courier"]if(Time(End_Time)=,Date(End_Time)+,End_Time)  /font[/quote]Do the same sort of thing for the start times.

Gareth

Hi Grant,

Using Summary Count function worked.

Exported, Lookup and calculate results.

Thanks.

I need a suggestion for another issue.

Sample of present Monarch Table below.

I need to do the following:

If Start time is 12:00:00 AM then needs to be 8:00:00 AM

If End time is 11:59:59 PM then needs to be

4:30:00 PM

All other times stay the same.

I tried using the IF function but received operand errors.

The time has both the Date and Time.....the Date always remains the same....just the time needs to change.

Ideas?

Thanks again,

[font="courier"]Assembly     Qty     UNIT TIME     Start     End     Assembler     ELAPSED TIME     B/U #     MINUTES     COMMONS

157-089     40     1.05     2006-03-10 2:02:02 PM     2006-03-10 2:43:37 PM     Darcie     2495          42     1

157-089     40     2.18     2006-03-10 10:32:52 AM     2006-03-10 12:00:00 PM     Leah     5228     115698     87     1

157-089     40     17.25     2006-03-10 12:30:00 PM     2006-03-10 11:59:59 PM     Leah     41399     115698     690     1

157-089     40     17.93     2006-03-11     2006-03-11 11:57:10 AM     Leah     43030     115698     717     1

157-092     40     0.65     2006-03-10 3:39:02 PM     2006-03-10 4:05:13 PM     Brian     1571          26     1

/font[/quote][/b][/quote]

• ###### Time Span

Sorry, saw the explanation post and thought it referred to the earlier one - the question above it completely failed to register with me.

I blame the aging process.

On the other hand the benefit is that you get a much more effective solution than I would have come up with I suspect.

• ###### Time Span

Gareth,

Referring to your suggestion to Brad as quoted below, it works well when using and "=" operator.

Quote

if(Time(End_Time)=,Date(End_Time)+,End_Time)

Unquote

Why can I not adapt the above replacing the "=" with "&gt;" so that if endtime in an example like the one above is greater than 11:59:59 I could ideally get it default to start time on the following day say 08:00:00 AM with a +1 added to the date(end_time).