• 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

Hi Narayan,
Yes, the calculated string would be in column C on "Customer vs Job Hours" sheet. The data would come from Column E of "JZU October 2013" tab.

(Column I shows the Job Departments, even though the heading says Job legend.)

Regards,
Sajan.
 
Hi Narayan and Sajan!
I uploaded the spreadsheet again, discussing the formulas i have tried using and their outputs.
Yes, Sajan is right. There are multiple JOB# or Job codes from the other tab that needs to display or populate, when I select a Start and End Date.

Thanks! hope you can help out :)
 

Attachments

  • Workshop Production Schedule Timetable.xlsx
    427.8 KB · Views: 8
Hi ,

I am sorry but it's all too confusing for me ; if you can take some more time to explain what you want done instead of giving the formulae , it would help.

1. You have a start date of September 30 , and an end date of September 30 , in A4 and B4. Will this always be true that you will look at only one date , or will you want the report for a range of dates , with a start date of X , and an end date of Y where Y is any number of days away from X ?

2. You already have formulae in columns D and E ; these are returning some outputs based on what is available in column F.

3. Will you enter the data in column F manually , or do you want that this should also come through the macro ?

4. Is each entry in column D and E a cumulative figure based on what is entered in column F e.g. there will be one cumulative figure for PM , one cumulative value for M , and so on ?

5. What is the relation between what is to be put in column C with what is in column F ? Is it one-to-one , one-to-many , many-to-one or many-to-many ? i.e. is there a one-to-one relationship between the Job Department and the Job # , or will there be many Job # in one Job Department , or will there be many Job Departments against one Job # ?

Narayan
 
Hi Narayan,
Sorry if I keep on confusing you on this.
i have loaded another file and explain the output i wanted to have.
I hope this better explain things on this. It is on the Customer Vs. JobHrs Tab
That if I chose Sep 30 - Oct 01, for the x start date and x end date respectively, it will output on Col C all available
job#'s and on Col D it will output corresponding time.
All the best!
nejjoan14
 

Attachments

  • Workshop Production Schedule Timetable.xlsx
    430.6 KB · Views: 6
Hi Somendra,

Thanks for your help. Yes this is actually what I wanted to happen. to display all the Job#'s done for start x date up to end x date with the corresponding time. Thanks for your help.
Can you explain how it happens?

Best regards,
nejjoan14
 
@Sajan,
@Narayan,
@Somendra,
@CHANDOO ORG
You're team is so great! thanks for helping me out & other newbies here!
All the best! Cheers!
 
Hi Nejjoan14,

Glad the result met your expectation.
See basically there are two steps in getting the solution. Firstly to find number of data in the date range. Which is done through COUNTIFS function in cell G15.
Secondly, I had use below formula to retrieve data for both JOB# and time.
Code: 
=IF(ROWS($D$23:D23)<=$G$15,INDEX('JZU October 2013'!$E$9:$E$197,SMALL(IF('JZU October 2013'!$A$9:$A$197>='Customer vs Job Hours'!$A$4,IF('JZU October 2013'!$A$9:$A$197<='Customer vs Job Hours'!$B$4,ROW('JZU October 2013'!$E$9:$E$197)-ROW('JZU October 2013'!$E$9)+1)),ROWS($D$23:D23))),"")

In this formula first there is a check which will be done to check that no. of total data is retrieved and it should not result in an error when you drag it down. Then there is INDEX function which will return job#. Now SMALL function will check if the data range is in required data range and return the Row no. of that data. Now once one data is recovered, we need next data so the row number is incremented by last ROWS function.

Hope this is clear now, if not then please ask.

Somendra.
 
Hi nejjoan14,
I thought you had wanted to show multiple job numbers (that matched a given job department and start/end date combination) in a single cell. I had clearly misunderstood your requirement, but am glad you received a solution!

Cheers,
Sajan.
 
Thanks Somendra for explaining things. So meaning these 2 formulas really goes together? Is there a way we only get the JOB#'s with time, as we do not need the LUNCH and TEA time to compute for the job hours done?

Hope you can still help me... :)

Best,
nejjoan14
 
@nejjoan14
I got your point, basically you want to extract only productive or working hour. I tried a formula for not including any type of break's, but it is going way bigger. I will suggest you to use filter to get only productive Job# and their respective time.

On the last line you can use SUBTOTAL() function which will give you total hours, which will change when you will change the filter value.

Attached file is for sample.
 

Attachments

  • Workshop Production Schedule Timetable-4.xlsx
    416.7 KB · Views: 7
Thanks a lot, Somendra!
I got your point, this is manual filter, and that all filtered cells will not appear and counted or computed on the sum below!

Thanks for your help!
I will check my spreadsheet and test more tabs per personnel.

ALL THE BEST!
nejjoan14
 
You must log in or register to reply here.
Back
Top