Calculating time period frequency

[Fajrr]

Hi all,

Whata great resource this is and I'm hoping someone out there will be able to help.

Essentially, i'm trying to establish overtime frequency from data and would like to answer the question, for example, overtime is likely to commence at 2am and last for 45 minutes.

I have tried using the countif function but cannot crack it.

cheers

fajrr

[David A Coop]

G'day Fajrr,

There is a lot that is unclear here I'm afraid!

1. What are you trying to add up? These dates are all over the place, so I assume names are missing.
2. You said OT kicks in after 2:00am, but these numbers are paying OT for earlier than that.
3. I can't see a pattern to the OT rate. eg. I see double time being paid on Saturdays but time and a half on Sundays. This does not seem correct to my way of thinking.
4. Paid hours is worked hours times the "Hour Type" - not just the hours worked past 2:00am.

Please clarify - again, how do you define the OT frequency??

Regards,

David

[Fajrr]

Hey David,

Thanks for the attempt.

The initial data set was probably too noisy. I'm trying to have the data depicted in a graph which shows which times between 23:30:00-07:30:00 have the greatest frequency of overtime.

Thanks again.

Fajrr.

[David A Coop]

Still not clear on what you want!

Your data isn't helped by all the fields being text, but you want to analyse time.

In the attached, I have created a unique list of times recorded in column F.

I then converted the text times to TIME in columns G H an J, and used SUMPRODUCT to get the count if each unique time where the end time was after 2:00.

This make no sense of course where the start time is after 2:00, but your request is unclear. I just wanted to give you a clue as to why you will be struggling with COUNTIF.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[newdoverman]

Maybe this will help you.
All of the times that you have listed in the range are TEXT and had to be changed to Excel times by using the TIMEVALUE function.
Enter in F3 and fill across and down

Please Login or Register  to view this content.

The hours were then calculated with this formula entered in column H
Enter in H3 and fill down and format as h:mm

Please Login or Register  to view this content.

[Fajrr]

Thanks everyone for there help,

I have achieved an output, not as advanced as I would have liked but usable.

Thanks again