How to calculate time spend on a task

[fmessier]

Hello, this is my fist time on the forum, How can I calculate the time spend with a date, start time and End time by Merch type and banner for each wave (Each wave has a 2 tasks, a Replenishment task and a pick task)

Wave 1 - Footwear - Banner 1
Wave 2 - Footwear - Banner 2
Wave 3 - Accessories - Banner 1
Wave 4 - Accessories - Banner 2
Wave 5 - Handbags - Banner 1
Wave 6 - Handbags - Banner 2



Thank you in advance for your help!!

Fred

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[fmessier]

Hello,
I am trying to find a formula that will give me the Minimum Start date/Start time and the Maximum Confirmed date/Confirmed time of each wave.

See attached file.

Thanking you in advance for your help!

[muhammadridho30]

I am confused by the title and the request,

the title says : calculate time spend on each wave (confirmed date/time - start date/time)
the request says : give me the Minimum Start date/Start time and the Maximum Confirmed date/Confirmed time, (I think it's automatically created or manually created when the wave begins(?) )

maybe you can give more explanation about the calculation? e.g raw data (edited/non sensitive) and how it looks if it's done (manually)

[AliGW]

MIN Date/Time in N2:

=MIN(IF($C$2:$C$103=C2,$J$2:$J$10))+MIN(IF($C$2:$C$103=C2,$K$2:$K$10))

MAX Date/Time O2:

=MAX(IF($C$2:$C$103=C2,$J$2:$J$10))+MAX(IF($C$2:$C$103=C2,$K$2:$K$10))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Calculation in P2:

=O2-N2

Formatting for columns N and O:

dd/mm/yyyy hh:mm:ss

Formatting for column P:

[hh]:mm:ss

[Glenn Kennedy]

Simpler non-array (just use Enter) alternative:

=AGGREGATE(15,6,($J$2:$J$10+$K$2:$K$10)/($C$2:$C$10=C2),1)

and

=AGGREGATE(14,6,($J$2:$J$10+$K$2:$K$10)/($C$2:$C$10=C2),1)

[fmessier]

Hello AliGW,

How can I link the MIN and MAX time with the appropriate wave number?


Thank you in advance,

Frederic

[Glenn Kennedy]

Not sure what you mean... already the Min/max are specific for each wave number in the solutions offered both by Ali & myself...

[fmessier]

Hello Glenn,
the thing is that I need the MIN and MAX on a unique row with the wave number. Presently for example, wave 4000018357 is on 8 rows.

Thank you
Frederic

[Glenn Kennedy]

Is this what you mean? If not, then please do what you should have done at the start.... show us your expected results, calculated manually, on your sample sheet.

[fmessier]

Hello Glenn,
it works, thanks!

In a formula, see below copy from the last attached file you sent. If I calculate the time spent to do wave 4000018362, it give me 26:56 minutes, but this time is wrong as we only have a shift of 8 hours a day (7:00 am to3:00 PM)
So how can I calculate the real time spent to do the wave?

See attached file

[JeteMc]

Try the following in cells P2 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[fmessier]

Hello JeteMc,
Thank you for your help!


Now, what is the best way to do the following;

• To remove the employee break from the wave time.
• To show that time on a Key Performance Indicator/Graph?

Thank you,
Frederic

[JeteMc]

Best is a subjective term.
However, we may be able to propose a method of removing breaks from wave time.
I feel that we will need to know what the break times are and preferably see a sample of manually calculated results.
As to the graph, the most self-explanatory may be a clustered column chart produced from a pivot table that only utilizes Wave in the Rows area and Sum of Wave Time in the Values area.
Let us know if you have any questions.

[fmessier]

Hello JeteMc,

I have attached the file that includes the Work day shift and the breaks.

thanking you in advance for your help!

Frederic

[JeteMc]

This one will require some testing and modification, however it seems to work for the sample:

Please Login or Register  to view this content.

Note that a column (A) for team has been added to the
Let us know if you have any questions.