How to collate total OT details by day and monthly using the data on the attached file

[Lewin Bolongaita]

I am trying to get the total OT details but the OT details are not on the same row. I usually use VlookUp, but it is not applicable if the raw data are on different rows.

Thanks in advance.

 

[NARAYANK991]

Hi ,

As a first step , please upload a workbook which has had some cleaning up of blank rows and columns.

I am re-uploading your original file after removing the blank rows.

Whoever wishes to help out can use this file.

Narayan

 

[Nebu]

Hi:

I am not completely sure about your requirements. I have constructed the formulas as per my understanding with the help of helper columns. Just check whether the formulas are giving you the expected results.

Note: I have converted the time values into decimals say 1:30 hrs will be converted to 1.5 hrs.

Thanks

 

[David Evans]

I am trying to get the total OT details but the OT details are not on the same row. I usually use VlookUp, but it is not applicable if the raw data are on different rows.

Thanks in advance.

What is the source of your "raw data"? It looks like a report file that has been pushed out to excel, but is truly a printout.
You can either go to the source and produce an excel file that produces a one row for each person.

It is possible to use PowerQuery and the M language to manipulate such a file, but I think you'd be better off working on the application that generated the raw file to get it into a format that's easier to work with .... It will save you a lot of effort in the long run ...

 

[Lewin Bolongaita]

What is the source of your "raw data"? It looks like a report file that has been pushed out to excel, but is truly a printout.
You can either go to the source and produce an excel file that produces a one row for each person.

It is possible to use PowerQuery and the M language to manipulate such a file, but I think you'd be better off working on the application that generated the raw file to get it into a format that's easier to work with .... It will save you a lot of effort in the long run ...

The source data came from a client application wherein we do not have admin privileges. When we export the data in excel file, the attached raw file is what we are getting to provide the OT details.

I am not familiar with PowerQuery and the M language, but i will check on that.

Thanks a lot.

 

[Lewin Bolongaita]

Hi:

I am not completely sure about your requirements. I have constructed the formulas as per my understanding with the help of helper columns. Just check whether the formulas are giving you the expected results.

Note: I have converted the time values into decimals say 1:30 hrs will be converted to 1.5 hrs.

Thanks

Thanks a lot, i was able to improve my assignment because of your help.