• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Production Time Sheet

N

nejjoan14

New Member
Hi Guys,
And just in the case, if we click on a regular filter. Can it sum up everything on the formula?

I need help with my project, I have columns for the date, start time, end time, Job dept, job#, tasks, total time and the equivalent number of hours worked.

I used these formulas which works fine.
=SUMIF($D$5:$D$53,A49,$H$5:$H$53)*24
=SUMIFS(H5:H46,D5:D46,"PM",I5:I46,1)*24
=SUMPRODUCT(($H$5:$H$46)*($D$5:$D$46=A49)*($I$5:$I$46=1))*24
However, I need to pull just for the date, Job dept, and the total hours of work. can we do that? then sum up the total worked hours?
 

Attachments

  • Workshop Production Schedule Timetable.xlsx
    404 KB · Views: 13
Hi nejjoan14,

Can you please elaborate, I cannot see where you are using the formulas and also give an example of what you are trying to achieve.

cheers
kanti
 
Hi Kanti,
I have attached my spreadsheet on my first post. on the JZU 14-18 October 2013 TAB, I need to create a formula, based on the dates, that automatically computes for the total hours. However, it also needs to display the BREAK, PM, M, Admin Personnel Work all AUTOMATICALLY...
C:\Documents and Settings\jeugenio\My Documents\Masterflow\Screenshots\ExcelQ3
 

Attachments

  • ExcelQ3.JPG
    ExcelQ3.JPG
    106.9 KB · Views: 15
  • Workshop Production Schedule Timetable.xlsx
    407.5 KB · Views: 11
Hi nejjoan14,
I am not sure I understand your comment "based on dates" since I do not see any dates to use as a condition.

But if you are merely looking to sum the total hours worked for PM, Break, etc., try the following formula in cell I49 and copy down:
=TEXT(SUMIF($D$5:$D$46,$A49,$H$5:$H$46),"hh:mm")

The above shows the sum as hours and minutes. If you would rather see the amounts as fractions of hours, you can multiply the SUMIF(...) with 24

If you are looking for something else, please clarify.

Cheers,
Sajan.
 
Hi nejjoan14,

please check attached file.

Could not load file, I got an error message, but please enter the following formula in cell I49, of the JZU 14Oct TAB

=SUMPRODUCT(SUBTOTAL(9,OFFSET($H$5,ROW($H$5:$H$46)-ROW($H$5),,1)),--($D$5:$D$46=A49))*24
 
@ Kanti
@ Sajan
Thanks for your comments, it both works fine. Is there a possible way to filter this from dates? and automatically give results for the categories i hv e.g. PM, M, E, Admin Personnel, BREAK, and UN-ALLOCATED HRS? do you think it will be possible if I select a particular date i.e. 18 October and will populate all these data needed?
 
Hello nejjoan14,
I am not sure I understand what you mean by "filter this from dates". Are you wanting to add a condition where A5:A46 matches a given date, in addition to the categories like PM, M, E, etc.?
 
Hi Sajan,
Yes, that the one I meant to say. Thanks for making it more clear. Do you think it's possible?
Regards,
nejjoan14
 
Hi nejjoan14,
Assuming the date you want to compare to is in cell B1, put the following formula in cell I49 and copy down:
=SUMPRODUCT((LOOKUP(ROW($A$5:$A$46), ROW($A$5:$A$46)/($A$5:$A$46<>0), $A$5:$A$46)=$B$1)*($D$5:$D$46=$A49)*($H$5:$H$46))*24

Cheers,
Sajan.
 
Hi Sajan,

Thanks for this. I am unsure if I have typed this wrong. I am trying to check the formula. please see screenshot.
 

Attachments

  • SumProd JobHrs w-date.JPG
    SumProd JobHrs w-date.JPG
    118.6 KB · Views: 6
Hi Sajan,
I got his now! It works. How about if I would fill up the entire column A with dates, and I would like to put up a date range. i.e. I have work from 01 - 31 October 2013 and I would only like to bill 01-15 October. I will have a date range, still summing up the PM, M, E, Admin Personnel, BREAK, and UN-ALLOCATED HRS (which we already resolved). Thanks for your extended patience!

All the best!
nejjoan14
 
Hi Sajan,
Can we set a date range. for example we enter a date on "BILLING DATE INCLUDES:"
e.g. Start Date "B3" and End Date "C3" is equal to the date range formula
=IF(Date(10/1/2013>=10/12/2013), "dd/mm/yyyy")
if a work week starts from 30 Sep-Oct 4 2013 (this is the date range to consider).
Hope you can get back to me next week. Have a great weekends!

Best!
nejjoan14
 
Hi nejjoan14,
If column A does not have dates filled in, you would use something like:
=SUMPRODUCT((LOOKUP(ROW($A$5:$A$46), ROW($A$5:$A$46)/($A$5:$A$46<>0), $A$5:$A$46)>=$B$3)*(LOOKUP(ROW($A$5:$A$46), ROW($A$5:$A$46)/($A$5:$A$46<>0), $A$5:$A$46)<=$C$3)*($D$5:$D$46=$A49)*($H$5:$H$46))*24

If column A has dates filled in for every cell, then you could use something like:
=SUMPRODUCT((A$5:$A$46>=$B$3)*($A$5:$A$46<=$C$3)*($D$5:$D$46=$A49)*($H$5:$H$46))*24


Both formulas assume B3 = StartDate, and C3=EndDate

Cheers,
Sajan.
 
@nejjoan14
Hi!
Sorry, been absent for a few days, but I "left" you in one of the better hands available. Welcome back whenever needed or wanted.
Regards!

@Sajan
Hi!
Thanks for resuming, ok, in fact almost for starting :p
Regards!
 
@ Sajan, thanks a lot!
Btw, I still have few more question. They wanted to add another TAB (which I did), and the formula should populate or display the JOB#'s. I was trying to use the SUMPRODUCT formula, but it does not display it. Please see attached.
 

Attachments

  • Cust vs.Job Hrs.JPG
    Cust vs.Job Hrs.JPG
    77.6 KB · Views: 7
Hello nejjoan14,
From the image file you uploaded, I am gathering that you want to display the multiple job #s in a single cell. Doing that with formulas can get messy (and would involve a series of helper cells).

I suggest that you pursue a VBA based solution. Since VBA is not my forte, I am sure one of the forum VBA experts will chime in with a solution.

Cheers,
Sajan.
 
Hi Sajan & Team,
Thanks for helping me out! I am almost done with my first project! I am just waiting for other amendments from my sups!

I will be here to help put others too just in the case!

All the best!
nejjoan14
 
Hi Guys,

I know that Sajan, already gave me a hint that I will be needing help with VBA, though I have tried few formulas too. I am not a VBA person too, and re-starting to practice my excel capabilities. Hope you can help me figure things out about this.
 

Attachments

  • Excel Production Sheet-1.PNG
    Excel Production Sheet-1.PNG
    125.1 KB · Views: 7
  • Workshop Production Schedule Timetable.xlsx
    425.7 KB · Views: 6
Hi ,

I have not understood your requirement. It would be nice if you could explain in words :

1. Where do you want the formula , in which sheet tab , and in which cell ?

2. What should the formula do i.e. what should it output ?

3. Can you work out an example giving the input values , and the expected output value ?

Narayan
 
Hi Narayan,
In the previous post, the OP wanted to display the multiple job numbers in a single cell. For example, a single string that says "E2147, E2430" in cell C6. That is why I suggested a VBA approach.

-Sajan.
 
You must log in or register to reply here.
Back
Top