Register
Log In
Unanswered
Home
January 19, 2017
Offline
Need your expertise on this, in Overtime sheet is where we key in the daily OT for the staff.
at Summary, how to count the total each staff accumulated for each month?
Dashboards
Power BI
Power Query
April 21, 2015
Offline
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
Dashboards
Power BI
Power Query
April 21, 2015
Offline
I meant of course 'doesn't seem too difficult'
Dashboards
Power BI
Power Query
April 21, 2015
Offline
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?
Dashboards
Power BI
Power Query
April 21, 2015
Offline
And oh yes: I multiplied the hours with 24 so you have normal numbers.
January 19, 2017
Offline
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
January 19, 2017
Offline
attached
Dashboards
Power BI
Power Query
April 21, 2015
Offline
OK, I'll give it a try later this day (here in Netherlands) and come back to you maybe this evening.
Dashboards
Power BI
Power Query
April 21, 2015
Offline
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!
Dashboards
June 25, 2016
Offline
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
Dashboards
June 25, 2016
Offline
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.
Most Users Ever Online: 57
Currently Online:
8 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Sethu nath
Jose Alvarado
James Villapudua
Sheri O'Connor
Noreen Anagnos
Connie DeMercurio
Shanna Steinberg
Holly Seamons
Mark Cooper
David Matanana
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
