I'm working on it (doesn't seem to difficult, but always 'afraid' Sunny already presented the solution :-)).

But some questions to get it more clear:

1) Did you try to do it yourself already and did you encounter some problems? I ask this because the way column A is filled, doesn't help. It's much easier (maybe I must say it's the only right way) if you give in the dates on each row. OK, that can be solved of course.

2) It's confusing (for me) that in the data sheet the numbers of the staff people in column B are different from the numbers on the summary tab. Now don't we do much with these numbers (I think it's better to search and sum by name), but is there a reason to have different numbers here?

3) Do I understand it right that the 'overtime' is the time in column F?

thanks,

Frans

I meant of course 'doesn't seem too difficult'

OK, did try to give an answer but by adding the attachment my Edge 'hung'. Another try.

Here what I was thinking of.

I made a helper column for the month to make it easier to understand.

You can expand the formulas in E33:E44 to the right. That works well.

Can you go on on this way, or do you think of something else?

And oh yes: I multiplied the hours with 24 so you have normal numbers.

wow, great answer.

However I encounter some issue when I insert the formulas to my files, 

Dear Master,

I had insert my files, it will be great if you are able to insert the formulas?

With best respect, CW

attached

OK, I'll give it a try later this day (here in Netherlands) and come back to you maybe this evening.

Hi Chng.William,

I see some of the issues.

First: I think you are on the right way!

On the 'January' tab you can skip column A now. You inserted the data in column B in the wrong row. So row 2 isn't counted now. You can repair that easily I think now you know what's wrong there.

On the 'Summary' tab I see the Formula in the January Column is correct and you followed my advice to use the same numbers for the persons on this tab as on the month tabs. Great!

You can modify the formula a little because you make the monthly tabs with no other data on it. So it isn't necessary to specify the ranges, you can use the columns now. Thus $H$2:$H$1125 can be changed in H:H and so on for the columns.

I also see the counting problem. I think (but I'm no expert on these things, sorry (and I hope Mynda or Sunny can help us here)) this has something to do with the way time is handled in your Excel version and language. I see only that the counting doesn't work wel in all ways. For instance I tried to count on the januari sheet the hours of person 1 and that doesn't work good as well, where in my own version with the same times it's normal and correct.

So my thinking is this has to do with the settings (time in AM and PM which isn't used here and maybe something else).

When this part is solved (I'll try to search for something, but my hope is on Mynda and Sunny or someone else of the MOTH-team) I think you have a good working sheet. Only thing is that you can't copy the formula on the summary tab from the January to the Februari column because you have to refer to another tab! So that's a little 'copy and paste' and a little 'modifying by hand'.

Good luck!

Hi chng.william168 and Frans

Format is just one of the issue in the original file. The biggest problem is the value entered in the time columns (F and G).

Notice that the time values in those cells came with an extra date!!. You can see it if you press F2 on the cell or look at the formula bar.

Example : JAN worksheet

Cell F2 = 18/06/2017  06:00:00 PM instead of 06:00:00 PM

Cell G2 = 19/06/2017  10:37:00 PM instead of 10:37:00 PM

That is why you can never get the correct answer. I cleaned it up by using the formula =F2-INT(F2) etc to get rid of the date portion. As you may already know, dates are whole numbers while time are decimals.

Furthermore cell B2's value should be 1 instead of the text MONTH.

I have attached a revised sample that you can refer to. I am using the [h]:mm custom format for the time.

Make sure that the JAN to DEC worksheets are the same (format wise) otherwise it will be more difficult to do the summing.

Hope this helps.

Sunny

To further add, in such a situation, I would prefer to have all my data in a single worksheet and then use a Pivot Table to summarize them. No formulas involved, only some custom formatting of the time and grouping of the dates into months.