hours limits

[david gabra]

Hey,

Based on regulations, I need to check hours of staff and if these breaches limits

1) if any employee on a given day worked more that 8 hours and less that 10 i need to identify that

2) if an employee worked more that 10 hours on a given a day I need to identify that.

3) an employee can charge time on more that one line , i mean - from 8 to 11 am and then from 1pm to 8 pm

4) if an employee has 1 line only and charged more that six hours - need to minus 1 hours from the total hours for that given day.

if takes my life very hard.

Please help - i have exhausted all my sumif and pivots.

Excel attached - please help with excel magic.

 

[GraH - Guido]

Question: do you have power query on board (get & transform in later versions)?

 

[david gabra]

No - simple excel

 

[GraH - Guido]

Hi david gabra,

Changes are you might not be aware you'd have Power Query after all.
If your have office 2010 or upwards (not Mac), it is available as add-in.

The result can be a table as this (I hope I got the requirements right)


The applied steps (almost all are "recorded" with simple mouse clicks)

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Emp Code", Int64.Type}, {"Company Name", type text}, {"Entry Time", type time}, {"Exit Time", type time}}),
  ExitTmeEntryTmeSubtraction = Table.AddColumn(#"Changed Type", "Subtraction", each [Exit Time] - [Entry Time], type time),
  GroupPerDayEmpComp = Table.Group(ExitTmeEntryTmeSubtraction, {"Company Name", "Emp Code", "Date"}, {{"Count", each Table.RowCount(_), type number}, {"Total Hours", each List.Sum([Subtraction]), type duration}}),
  NewTime = Table.AddColumn(GroupPerDayEmpComp, "New Time", each [Total Hours]-#duration(0, 1, 0, 0), type duration),
  NewTimeFinal = Table.AddColumn(NewTime, "New Hours", each if [Count] = 1 and [Total Hours] > #duration(0, 6, 0, 0) then [New Time] else [Total Hours]),
  RemoveHelperCols = Table.RemoveColumns(NewTimeFinal,{"New Time"}),
  WorkRegime = Table.AddColumn(RemoveHelperCols, "Work Regime", each if [New Hours] > #duration(0, 10, 0, 0) then "Worked more then 10 hours" else if [New Hours] >= #duration(0, 8, 0, 0) then "Worked 8 - 10 hours" else "Worked regular hours"),
  FinalDataTypes = Table.TransformColumnTypes(WorkRegime,{{"New Hours", type duration}, {"Count", Int64.Type}, {"Work Regime", type text}})
in
  FinalDataTypes

 

[Peter Bartholomew]

Using formulas …

The main thing that may confuse (other than the use of structured references) is the defined Name 'FinalExitTime' that refers to the formula

= MAX(IF(Table1[Emp Code]=Table1[@[Emp Code]], Table1[Exit Time]))

This is done to ensure that the parameter is evaluated as a proper array calculation.

 

[david gabra]

thank you! great.